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.

Answer 1

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.