Here are three examples of returning a list of stored procedures that reference a specific table in PostgreSQL.

The information_schema.routines View

We can query the information_schema.routines view to see if any of the definitions contain the table name.

Example:

SELECT
    routine_name,
    routine_body,
    routine_definition
FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
AND routine_schema = 'public'
AND routine_definition ILIKE '%artists%';

In this example, I checked to see if any of the stored procedures had the text artists in their definition.

The ILIKE operator makes the match case-insensitive according to the active locale. This is a PostgreSQL extension, and it’s not part of the SQL standard. For a case sensitive match, use LIKE.

Note that it’s not perfect, in the sense that it could return false positives if the text is in the procedure, but it isn’t a table. To mitigate this risk, you could perhaps refine the filter criteria.

The pg_proc Catalog

The pg_catalog.pg_proc catalog stores information about functions, procedures, aggregate functions, and window functions. We can therefore query like this:

SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    p.prosrc AS definition
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prokind = 'p'
AND n.nspname = 'public'
AND p.prosrc ILIKE '%artists%';

In this case, we join the catalog with the pg_catalog.pg_namespace catalog to filter the results to only procedures with the public namespace.

We can alternatively use the pg_get_functiondef() function to get the definition. This function actually reconstructs the creating command for the stored procedure. It’s a decompiled reconstruction, not the original text of the command. This results in a CREATE OR REPLACE PROCEDURE statement for the stored procedure.

Example:

SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prokind = 'p'
AND n.nspname = 'public'
AND p.prosrc ILIKE '%artists%';

Get the Specific Line that References the Table

We can modify our query to include the line number that references the table:

SELECT *
FROM (
    SELECT 
        proname AS stored_procedure, 
        row_number() OVER (partition by proname) AS line_number, 
        textline
    FROM (
        SELECT 
            proname, 
            unnest(string_to_array(prosrc, chr(10))) AS textline
        FROM pg_proc p
        JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE nspname = 'public'
        AND prosrc ILIKE '%artists%'
        ) lines
    ) x
WHERE textline ILIKE '%artists%';

This example is based on a Stack Overflow answer by Klin.