List PostgreSQL Functions with pg_proc

List PostgreSQL Functions with pg_proc

Where information_schema.routines delivers a SQL-standard view of functions and procedures — portable across databases but deliberately incomplete — pg_proc gives the full PostgreSQL catalog row: language, source code, argument modes, cost estimates, and the prokind flag that distinguishes regular functions from aggregates, window functions, and stored procedures in a single query.

Purpose and Overview

Every function, procedure, aggregate, and window function created in a PostgreSQL database has a row in pg_proc. The catalog stores the complete definition: the function name, the schema it lives in, the language it was written in, the source text, the argument list with modes and default values, the return type, and planner hints such as estimated cost and expected row count. It is the authoritative source for everything a DBA or developer needs to audit the stored-code layer of a database.

The information_schema.routines view exists for SQL standard compliance and is useful for cross-database portability. But it deliberately omits PostgreSQL-specific detail: there is no source code column, no language identifier, no cost estimate, and no way to distinguish aggregates from window functions from ordinary functions. When the goal is a production audit rather than a standards-compliant query, pg_proc is the correct starting point.

Practical scenarios that drive a function inventory include pre-migration reviews — which schemas have functions, and in which languages? — security audits around SECURITY DEFINER functions that run with elevated privileges, code reviews after a team member leaves, and dependency analysis before dropping a schema or changing a type. A query against pg_proc joined to pg_namespace and pg_language returns all of that in one result set.

Sample Code

 1SELECT
 2    n.nspname                                     AS schema_name,
 3    p.proname                                     AS function_name,
 4    pg_catalog.pg_get_function_arguments(p.oid)   AS arguments,
 5    pg_catalog.pg_get_function_result(p.oid)      AS return_type,
 6    l.lanname                                     AS language,
 7    CASE p.prokind
 8        WHEN 'f' THEN 'function'
 9        WHEN 'p' THEN 'procedure'
10        WHEN 'a' THEN 'aggregate'
11        WHEN 'w' THEN 'window'
12    END                                           AS routine_kind,
13    p.prosecdef                                   AS security_definer
14FROM
15    pg_catalog.pg_proc     p
16    JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
17    JOIN pg_catalog.pg_language  l ON l.oid = p.prolang
18WHERE
19    n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
20ORDER BY
21    schema_name,
22    function_name;

Notes: Works on PostgreSQL 11 and later as written; the prokind column was added in PostgreSQL 11. On PostgreSQL 10 and earlier, replace the CASE p.prokind ... expression with CASE WHEN p.proisagg THEN 'aggregate' WHEN p.proiswindow THEN 'window' ELSE 'function' END. The WHERE clause excludes built-in catalog schemas; remove or adjust it to include system functions. Superuser or membership in pg_read_all_data (PostgreSQL 14+) is required to read source code for functions owned by other roles.

Code Breakdown

The query joins three catalog tables and uses two built-in formatting functions to produce human-readable output without reconstructing OID arrays by hand.

Joining pg_namespace for Schema Names

1JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

pronamespace stores the OID of the schema that owns the function. Joining to pg_namespace on that OID resolves it to nspname — the schema name visible in \df output. Without this join, the only identifier available is a raw OID, which is not useful for an audit.

Joining pg_language for the Implementation Language

1JOIN pg_catalog.pg_language l ON l.oid = p.prolang

prolang is the OID of the procedural language the function body is written in. PostgreSQL ships with built-in language entries for sql, internal, and c; installed extensions add languages such as plpgsql, plpython3u, and plv8. The join returns the language name directly rather than requiring a separate lookup — and this column is the one most relevant to security audits. Untrusted languages (c, plpython3u) require superuser to create and deserve extra scrutiny.

pg_get_function_arguments and pg_get_function_result

1pg_catalog.pg_get_function_arguments(p.oid)  AS arguments,
2pg_catalog.pg_get_function_result(p.oid)     AS return_type,

The raw argument and return type columns in pg_proc are OID arrays (proargtypes, proallargtypes, prorettype). Reading them directly requires joining repeatedly to pg_type and reconstructing the argument list by hand. These two built-in functions do that work in one call each, returning the argument list and return type in the same readable format as \df — integer, text, boolean rather than a sequence of OIDs.

The prokind Column

1CASE p.prokind
2    WHEN 'f' THEN 'function'
3    WHEN 'p' THEN 'procedure'
4    WHEN 'a' THEN 'aggregate'
5    WHEN 'w' THEN 'window'
6END AS routine_kind

prokind is a single character that categorizes the object. Functions (f) are the most common case. Procedures (p) are callable with CALL and support transaction control inside the body. Aggregates (a) are functions like sum() and count() that operate on a set of rows. Window functions (w) are functions used with OVER (...) clauses. Separating these four classes matters for an audit because they have different call semantics, privilege requirements, and dependency implications.

prosecdef: The Security Definer Flag

1p.prosecdef AS security_definer

When prosecdef is true, the function executes with the privileges of its owner rather than the caller. This is a standard privilege-escalation pattern — granting a low-privilege role access to a function that queries a table that role cannot read directly. It is also a common source of security vulnerabilities if ownership is not tightly controlled. Including this column in the result set makes it immediately visible which functions carry elevated execution context.

Key pg_proc Catalog Columns

proname and pronamespace

proname is the bare function name as supplied to CREATE FUNCTION. Because PostgreSQL supports overloading — multiple functions sharing the same name but with different argument lists — proname alone does not uniquely identify a function. The OID does. For output that matches what a DBA types in DDL, combining nspname, proname, and the formatted argument list from pg_get_function_arguments gives the fully qualified signature.

prolang and Trusted vs. Untrusted Languages

The language column is operationally significant beyond readability. PostgreSQL divides procedural languages into trusted (safe for any user, such as plpgsql and plperl) and untrusted (require superuser to create, such as c, plpython3u, and plperlu). Filtering the result to l.lanname IN ('c', 'plpython3u', 'plperlu') finds every function that required superuser to install — a focused security audit in one predicate.

prosrc: The Source Text

prosrc holds the function body for interpreted languages (plpgsql, sql, plpython3u). For compiled languages (c, internal) it holds the C function name rather than source code. This column is what grep-style code search across the stored-code layer uses: finding all functions that reference a particular table, call a deprecated function, or contain a particular string literal. It is absent from information_schema.routines entirely.

provolatile and Cost Estimates

provolatile records whether the function is i (immutable — pure, cacheable), s (stable — constant within a transaction), or v (volatile — may change per call). This classification affects whether the planner can hoist function calls out of loops or reuse results across calls, so a miscategorized volatility setting directly affects plan quality. The companion columns procost (estimated execution cost in cpu_operator_cost units) and prorows (estimated rows returned for set-returning functions) feed the planner's cost model for functions that appear in WHERE clauses and FROM expressions.

Practical Applications

Pre-Migration Function Inventory

Before migrating a schema to a new database or renaming a type, run the catalog query filtered to the target schema. The result lists every function that must accompany the migration, the language required on the destination server, and any SECURITY DEFINER functions that will carry elevated privileges to the new home. Catching these during planning avoids surprises at cutover.

Security Definer Audit

Filter the result to WHERE p.prosecdef = true and review the owner of each returned function. A SECURITY DEFINER function owned by a superuser, callable by a low-privilege role, effectively gives that role superuser-level access for the operations the function performs. A periodic audit of this list confirms that the ownership chain is intentional and that no function was created by a user who later departed with ownership never transferred.

Identifying Untrusted-Language Functions

Adding WHERE l.lanname IN ('c', 'plpython3u', 'plperlu') to the base query surfaces every function that requires superuser to create and has direct access to OS-level capabilities. These are the highest-risk objects in the stored-code layer from a security standpoint. The list should be short, well-documented, and reviewed at every major-version upgrade, because C functions link against the server binary and must be recompiled when the major version changes.

Finding All Functions That Reference a Specific Table

Searching prosrc ILIKE '%order_items%' finds every plpgsql or sql function that references a particular table name in its body. This is the catalog equivalent of a grep across stored procedures and runs in milliseconds. Use it before renaming or dropping a table to confirm no function body will break silently.

Generating a DROP FUNCTION Script for Schema Cleanup

After identifying orphaned or deprecated functions, pg_get_function_identity_arguments(p.oid) combined with nspname and proname produces the fully qualified signature needed for DROP FUNCTION. This is the correct approach rather than trying to reconstruct the argument list by hand, because overloaded functions share names and the OID-based formatting function always produces the right signature for the specific overload.

Version Compatibility

The pg_proc catalog has existed since PostgreSQL's earliest releases, but its column set has evolved significantly across major versions.

In PostgreSQL 11, prokind replaced the proisagg and proiswindow boolean columns used on earlier versions to classify routine types. The prokind column adds 'p' for stored procedures, which were also introduced in PostgreSQL 11 alongside the CALL statement. Queries that reference proisagg or proiswindow fail on version 11 and later; the prokind CASE expression in the sample code is the correct form for all currently supported releases.

PostgreSQL 14 introduced pg_read_all_data and related predefined roles that allow monitoring accounts to read catalog rows without full superuser grants. On PostgreSQL 13 and earlier, reading the source code of functions owned by other roles typically requires superuser.

The formatting functions pg_get_function_arguments() and pg_get_function_result() have been stable since PostgreSQL 8.4, making the query compatible across the entire supported version range once the prokind / proisagg branching is handled. pg_get_function_identity_arguments(), useful for generating DROP FUNCTION statements, was added in PostgreSQL 9.0 and has been stable since.

Best Practices

• Use pg_get_function_arguments() instead of raw OID arrays — reconstructing the argument list from proargtypes by hand is fragile across argument modes (IN, OUT, INOUT, VARIADIC); the formatting function handles all cases correctly.
• Filter out pg_catalog and information_schema — built-in functions produce thousands of rows rarely relevant to a user-space audit; the schema exclusion predicate keeps results actionable.
• Audit SECURITY DEFINER functions at every major-version upgrade — owner semantics can shift if a role is recreated or renamed during the upgrade process; verify the ownership chain after each migration.
• Check prolang for untrusted languages periodically — C and plpython3u functions require superuser to create but can be called by any grantee; the list should match documented, intentional usage.
• Use the OID as the stable identifier for DML — proname alone is not unique due to overloading; always filter or join on p.oid when building DDL against specific functions.
• Branch on prokind vs. proisagg for mixed-version fleets — a monitoring query deployed across PostgreSQL 10 and 11+ clusters needs both code paths; test each before rollout.

References

• PostgreSQL Documentation — pg_proc System Catalog — column-by-column reference for every field in the functions catalog, including prokind, prosrc, prosecdef, and cost-estimate columns.
• PostgreSQL Documentation — System Information Functions — documents pg_get_function_arguments(), pg_get_function_result(), and pg_get_function_identity_arguments().
• PostgreSQL Documentation — CREATE FUNCTION — SECURITY DEFINER, VOLATILE/STABLE/IMMUTABLE, COST, and ROWS options and how they map into pg_proc columns on creation.
• HariSekhon/SQL-scripts — PostgreSQL Administration Script Library — open-source collection of PostgreSQL DBA scripts covering catalog queries, function inspection, and administration tooling.