Postgres get function owner

Question

I would like to be able to get a table of function names and function owner names.

Currently, this query returns a list of functions for a given schema:

  SELECT  proname
  FROM    pg_catalog.pg_namespace n
  JOIN    pg_catalog.pg_proc p
  ON      pronamespace = n.oid
  WHERE   nspname = '<%= schema_name %>';

I can see there is a proowner column which returns the ID of the role.
https://www.postgresql.org/docs/10/catalog-pg-proc.html

What I would like is something that returns the name of the owner, I just don't know enough SQL yet to figure out how to JOIN the tables etc..

|function_name|function_owner|
______________________________
|func1        |func1_owner   |
|func2        |func2_owner   |

If someone could explain how to do this I would be very grateful!

Thanks

Laurenz Albe · Accepted Answer · 2020-02-06 05:39:03Z

There is a simple and a complicated way.

The complicated way is to join with pg_roles and get the user name from there.

But since PostgreSQL hackers don't want to type more than necessary, they came up with something else:

Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that is for example used in the proowner column of pg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just the oid, but the type output function, which is used for display of the type, renders it as the object's name.

Put that together with PostgreSQL's type cast operator ::, and you can do:

SELECT proname,
       proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would prefer oid::regprocedure over proname.

Thank you so much!! I learned something new about Postgres too as I thought I would :) — 4x0v7
– 4x0v7, Commented Feb 6, 2020 at 6:12

Stack Exchange Network

Postgres get function owner

1 Answer 1

Your Answer

Hot Network Questions

Postgres get function owner

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions