How to list out the user defined functions and procedures in PostgreSQL

Question

Trying to find out the stored functions and procedures on PostgreSQL. I found some of the solutions where "join" has been used. I am looking for an optimal solution/command to it.

score 21 · Accepted Answer · answered May 12 '16 at 18:21

If there is a command i really not know. I also use the join solution between pg_catalog.pg_proc and pg_catalog.pg_namespace for example, to list all the functions in one schema.

You can play with the join to get what you need. This query for example will provide you the commands to change the owner for all the functions on a schema:

SELECT 'ALTER FUNCTION '
            || quote_ident(n.nspname) || '.' 
            || quote_ident(p.proname) || '(' 
            || pg_catalog.pg_get_function_identity_arguments(p.oid)
            || ') OWNER TO owner_usr;' AS command
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
WHERE  n.nspname = 'your_schema';

And this one should list you all your user defined functions :

SELECT   quote_ident(n.nspname) as schema , quote_ident(p.proname) as function 
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace 
WHERE  n.nspname not like 'pg%'

Hope that helps.

Maybe the last line of code would better be modified to: `WHERE n.nspname not like 'pg_%'` (adding an underscore). Still not completely safe, but much safer already. — Tim B., May 17 '19 at 13:22

score 19 · Answer 2 · answered Feb 25 '19 at 09:15

19

You can get functions using meta command

\df

answered Feb 25 '19 at 09:15

user48623

191
1
2

1

Is there a meta command to dump the configuration of a function? Edit: nvm, you can use ' \sf [ function_name ]' – refriedjello Nov 26 '19 at 22:56
You might have to supply the db or schema to make this work: \df , or \df .*, so like \df my_db.* – Mallory-Erik Aug 17 '20 at 03:30

How to list out the user defined functions and procedures in PostgreSQL

2 Answers2