Show the user-defined functions and procedures in the database 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.

user48623 · Accepted Answer · 2019-02-25 09:15:41Z

22

You can get functions using meta command

\df

answered Feb 25, 2019 at 9:15

user48623

3212 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

refriedjello Over a year ago

Is there a meta command to dump the configuration of a function? Edit: nvm, you can use ' \sf [ function_name ]'

Mallory-Erik Over a year ago

You might have to supply the db or schema to make this work: \df <schema> , or \df <schema>.*, so like \df my_db.*

mortalis Over a year ago

postgresql.org/docs/current/…

n0099 · Accepted Answer · 2024-05-08 01:45:25Z

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.

edited May 8, 2024 at 1:45

n0099

1,6431 gold badge13 silver badges21 bronze badges

answered May 12, 2016 at 18:21

lsilva

8956 silver badges6 bronze badges

2 Comments

Tim B. Over a year ago

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.

n0099 Over a year ago

And now there's some functions with prefix _pg_ like information_schema._pg_expandarray

Super Kai - Kazuya Ito · Accepted Answer · 2024-01-16 14:51:15Z

This can show all user-defined functions and procedures in the current database:

\df

*Use p to show all user-defined procedures in the current database:

\dfp

*Use S to show all user-defined and system functions and procedures in the current database:

\dfS

*Use + to show all user-defined functions and procedures in the current database in detail:

\df+

*You can use p, S and + together to show all user-defined and system procedures in the current database in detail:

\dfpS+

And, you can show all user-defined and system functions and procedures in the current database with information_schema.routines and pg_proc as shown below:

SELECT routine_name FROM information_schema.routines;

SELECT proname FROM pg_proc;

*There is no way to show all user-defined (and system) functions and procedures in all databases at once.

Collectives™ on Stack Overflow

Show the user-defined functions and procedures in the database in PostgreSQL

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related