PostgreSQL中如何列出存储过程所使用的全部表？

方法1：利用系统依赖关系查询 #

这种方式基于PostgreSQL的系统依赖表，结果更准确，适合大部分常规存储过程：

SELECT DISTINCT
    p.proname AS procedure_name,
    c.relname AS table_name
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
JOIN
    pg_depend d ON p.oid = d.objid
JOIN
    pg_class c ON d.refobjid = c.oid
WHERE
    n.nspname = 'public' -- 替换为你的目标模式名
    AND c.relkind = 'r' -- 仅筛选普通表
    AND p.prokind IN ('f', 'p') -- f=函数，p=PostgreSQL 11+新增的存储过程
ORDER BY
    procedure_name, table_name;

说明 #

• pg_proc：存储所有函数/存储过程的定义信息
• pg_namespace：限定查询的模式（比如业务常用的public）
• pg_depend：记录数据库对象之间的依赖关系，这里用来关联存储过程和它依赖的表
• pg_class：存储表、索引等数据库对象的信息，relkind = 'r'指定只查询普通表

方法2：解析函数体捕获表名 #

如果存储过程使用了动态SQL（比如EXECUTE拼接的SQL），系统依赖表可能无法捕获到关联的表，这时可以通过解析函数体的方式匹配表名：

SELECT
    p.proname AS procedure_name,
    unnest(regexp_matches(p.prosrc, 'FROM\s+(\w+\.\w+|\w+)|JOIN\s+(\w+\.\w+|\w+)|INTO\s+(\w+\.\w+|\w+)', 'gi')) AS table_name
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    n.nspname = 'public' -- 替换为你的目标模式名
    AND p.prokind IN ('f', 'p')
    AND p.prosrc ~* 'FROM|JOIN|INTO'
ORDER BY
    procedure_name, table_name;

说明 #

• 正则表达式会匹配FROM、JOIN、INTO关键字后的表名，支持带模式前缀的表（比如schema.table）
• 用unnest把正则匹配的数组结果展开为单行记录
• 这种方法可能存在误匹配（比如匹配到关键字或变量名），需要根据实际存储过程的SQL风格调整正则规则

注意事项 #

• PostgreSQL 11之前没有专门的存储过程，所有可执行对象都是函数，此时只需将p.prokind IN ('f', 'p')改为p.prokind = 'f'
• 如果需要查询所有模式的存储过程，去掉n.nspname = 'public'这个条件即可
• 对于加密的存储过程（prosrc为空，probin存储二进制代码），无法通过解析函数体的方式获取表名，只能依赖系统关系查询

内容的提问来源于stack exchange，提问作者rohan