In Postgres, a procedure is a named block of code that encapsulates a sequence of SQL statements to perform a specific operation. A new stored procedure in Postgres can be created by executing the “CREATE PROCEDURE” command. However, to call or utilize an existing stored procedure, users are required to be familiar with the name of that particular procedure. For this purpose, various commands, catalogs, and views can be used.

This write-up presents a practical guide to getting the list of available stored procedures in a Postgres database.

How Do I Get the List of Existing Stored Procedures in Postgres?

Postgres offers several methods to get the list of available stored procedures. In this blog post, the below-listed methods will be discussed using suitable examples:

• Method 1: Using \df Command
• Method 2: Using information_schema.routines View
• Method 3: Using pg_proc Catalog

How to List All Stored Procedures Using \df Command?

“\df” is a meta-command that only works in the SQL Shell(psql). It retrieves the list of existing routines, including functions and stored procedures. For instance, in the following example, the “\df” command is executed to retrieve the list of all available user-defined functions and stored procedures:

\df

The following output snippet shows all available stored procedures and user-defined functions:

How to List All Stored Procedures Using information_schema.routines View?

The “information_schema.routines” is a standard view in Postgres that provides details about all routines of a database, including procedures and functions. To get the list of only stored procedures (excluding user-defined functions), specify the routine type as “PROCEDURE” in the WHERE clause:

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

The following snippet depicts that the “information_schema.routines” view successfully retrieves available procedures:

How to List All Stored Procedures Using pg_proc Catalog?

The pg_proc is a system catalog in Postgres that stores information about all the routines of a database, including functions and stored procedures. It has several columns that demonstrate detailed information about each routine. In the following example, the “pg_proc” catalog is used to get the list of available procedures in the public schema:

SELECT namespace.nspname As schema_name, 
procedure.proname As procedure_name
FROM pg_catalog.pg_namespace namespace
JOIN pg_catalog.pg_proc procedure ON 
procedure.pronamespace = namespace.oid
WHERE procedure.prokind = 'procedure'
AND namespace.nspname = 'public';

This is how users can get the list of stored procedures in Postgres.

Conclusion

To get the list of all stored procedures in a database, the “\df” command, “pg_proc”, catalog, and “information_schema.routines” view are used in Postgres. All these approaches retrieve the list of all the user-defined functions and stored procedures. This write-up has demonstrated various methods of listing stored procedures in PostgreSQL using suitable examples.