2

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

3

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.

| improve this answer | |
  • Thank you so much!! I learned something new about Postgres too as I thought I would :) – 4x0v7 Feb 6 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.