Recently I’ve frequently been finding myself needing to look up details of custom stored procedures in PostgreSQL databases. Here are a few helpful queries for that.
First, a simple list of the queries:
SELECT n.nspname AS schema, p.proname AS procedure FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND p.prokind = 'p';
Second, the owner of the procedures:
SELECT proname, proowner::regrole FROM pg_proc WHERE pronamespace::regnamespace::text = 'public';
Alternatively, the same information ownership information plus a bit of extra detail (You may need to change the user role name to match the owner(s) of the procedures):
SELECT a.rolname AS owner, p.proname AS name, p.prokind AS type, l.lanname AS language FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace JOIN pg_authid a ON a.oid = p.proowner JOIN pg_language l ON l.oid = p.prolang WHERE n.nspname = 'public' AND (a.rolname = 'user' OR a.rolname = 'postgres' AND l.lanname = 'plpgsql');
Finally, to see the definition of a stored procedure, so you can see what arguments it takes and what it actually does:
\sf procedure_name