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.