3 Ways to List All Functions in PostgreSQL

Here are three options for listing out all functions in a PostgreSQL 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 functions:

SELECT
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
AND
    routine_schema = 'public';

In this example, only functions with the public schema are returned. Feel free to include more columns if required.

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 = 'f'
AND
    n.nspname = 'public';

We filtered to a prokind of f to limit the results to just normal functions.

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

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 *key*

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

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