Postgres get function owner

Question 1

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 aproowner 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

Question 2

There is a simple and a complicated way.

The complicated way is to join withpg_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 theproowner column ofpg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just theoid, 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::regroleFROM pg_procWHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would preferoid::regprocedure overproname.

Question 3

Thank you so much!! I learned something new about Postgres too as I thought I would :)

Laurenz Albe 62.8k4 gold badges58 silver badges94 bronze badges · Accepted Answer · 2020-02-06 05:39:03Z

There is a simple and a complicated way.

The complicated way is to join withpg_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 theproowner column ofpg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just theoid, 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::regroleFROM pg_procWHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would preferoid::regprocedure overproname.

Thank you so much!! I learned something new about Postgres too as I thought I would :)

Movatterモバイル変換

Stack Exchange Network

Postgres get function owner

1 Answer1

Your Answer

Sign up orlog in

Post as a guest

Related

Hot Network Questions

Subscribe to RSS