PostgreSQL provides the command \dv to list all views. Is there a similar way to list all user-defined functions or perhaps just those function defined by a particular user? \sf requires you to know a function's name and it will provide a function's definition. \df lists all functions (and there are a lot). I'd like a way to just show a list of the functions I've defined.
Add a comment |
2 Answers
The best way to find such a query is to use psql with the --echo-hidden option. Then run the psql meta-command and you will see the query that is used.
For \df this is:
SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE WHEN p.proisagg THEN 'agg' WHEN p.proiswindow THEN 'window' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' ELSE 'normal' END as "Type" FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ORDER BY 1, 2, 4; You could adjust that by e.g. changing the where clause to:
AND n.nspname = 'public' Which is equivalent to \df public.*
If you check the documentation of pg_proc you will notice that there is a proowner column so you could also run:
SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types" FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_roles u ON u.oid = p.proowner LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE pg_catalog.pg_function_is_visible(p.oid) AND n.nspname = 'public' AND u.rolname = current_user --<< this limits the functions to those that the current user owns. 1 Comment
ehfeng
TIL
--echo-hidden. Mind. blown.I'm not sure if this command is entirely accurate but it appears to show me all functions owned by a particular role:
SELECT routine_name, grantee FROM information_schema.routine_privileges WHERE grantee = '<role>'; In any event, it gives me the results I would expect to see.