Stored Procedure Information In PostgreSQL

All information for procedures and triggers in PostgreSQL is stored in the pg_proc table in the system catalog. Here is how to get some of the more useful information.

While working with a PostgreSQL recently I needed to find all the information on stored procedures that had been written. pgAdmin has a great SQL pane that it generates for stored procedures with pretty much all the information needed, but only one at a time. The goal was to get the information on all the procedures. The best I could do in pgAdmin was run a backup creating just the DDL statements, but this used pg_dump, and didn't provide all the information that pgAdmin so kindly displayed nicely formatted. Plus there was no way to just get stored procedures, you get EVERYTHING. Almost 700k worth of DDL creation statements.

pgAdmin is open source, so what better way to figure out how they generate all that wonderful information than reading how they did it. After finding the code that handles the functions (pgFunction) and the GetSQL function that returns the nice and clean information in the SQL pane, it was clear that there was no simple command to get all the information we wanted easily from the database with just a simple dump command. I investigated further into where pgAdmin got the information and found that all the information was pulled mostly from the pg_proc table of the database, a table holding information on all the functions, including triggers in the database which are just a type of function. That includes the functions defined by PostgreSQL. We knew that already that it was stored in a table, but not all the details. Using the SQL in the pgAdmin code as a basis to get the information made things pretty easy. All that is needed is the OID of the schema with the procedures, which I will represent as in the SQL.

SELECT * from pg_proc proc JOIN pg_type typ ON proc.prorettype = typ.oid WHERE typ.typname <> 'trigger' AND proc.pronamespace = AND proc.proisagg = false ORDER BY proc.proname

This gives all the information on the procedure and the type of the procedure. Scale to your liking. Using the OID of the schema limits it to functions that are not defined by PostgreSQL and are specific to that schema. To remove triggers, which are a type of function, remove all with the type (typname) "trigger". Using the proisagg flag will show aggregate functions (true) or non-aggregate functions (false).