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.
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.