3 Ways to List all Stored Procedures in a PostgreSQL Database

by

Here are three options for listing out all the stored procedures in a Postgres database.

The information_schema.routines View

This view contains all functions and procedures in the current database that the current user has access to (by way of being the owner or having some privilege).

Here’s an example of returning a list of stored procedures:

SELECT
    routine_schema,
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'PROCEDURE';

Feel free to include more columns if required. You can get the procedure’s definition with the routine_definition column.

The pg_proc Catalog

The pg_catalog.pg_proc catalog stores information about functions, procedures, aggregate functions, and window functions.

We can join this with the pg_catalog.pg_namespace catalog to filter the results to only procedures with the public namespace:

SELECT
    n.nspname,
    p.proname
FROM 
    pg_catalog.pg_namespace n
JOIN 
    pg_catalog.pg_proc p ON 
    p.pronamespace = n.oid
WHERE 
    p.prokind = 'p'
AND
    n.nspname = 'public';

We filtered to a prokind of p to limit the results to stored procedures.

The possible values are f for a normal function, p for a procedure, a for an aggregate function, or w for a window function.

You can add the p.prosrc column to get the procedure’s definition. Or you could pass the p.oid column to the pg_get_functiondef() function. This function will produce a CREATE OR REPLACE PROCEDURE statement for the stored procedure.

The \df Command

When using psql, we can use the \df command:

\df

By default, this returns only user-created objects. You can alternatively supply a pattern or the S modifier to include system objects.

Here’s an example of providing a pattern:

\df *album*

That example narrows the results to just those functions/procedures with the text album in their name.

Note that this command also returns functions. The routine type (e.g. func, proc) is listed in a type column in the output.