Postgres get function owner

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 = '';


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

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.

StackExchange Database Administrators Q#259001, answer score: 20