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.