从PostgreSQL存储过程中提取Join信息的可行方案求助

以下几种方案可以帮你批量提取PostgreSQL中存储过程和函数里的JOIN关联信息：

方案1：利用系统目录+正则表达式快速提取 #

PostgreSQL的系统表pg_proc和pg_namespace存储了函数/存储过程的元数据，结合pg_get_functiondef()获取完整创建语句，再用正则匹配JOIN相关内容，适合快速批量扫描。

示例查询 #

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    -- 匹配所有JOIN类型及关联表，支持带schema的表名
    regexp_matches(
        -- 先移除注释避免误匹配
        regexp_replace(
            regexp_replace(pg_get_functiondef(p.oid), '--.*$', '', 'g'),
            '/\*.*?\*/', '', 'gms'
        ),
        '(LEFT|RIGHT|INNER|FULL|CROSS)?\s*JOIN\s+(?:([^\s".]+)\.)?([^\s",]+)',
        'gi'
    ) AS join_details
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    p.prokind IN ('f', 'p') -- f=函数，p=存储过程（PostgreSQL 11+）
    AND n.nspname NOT IN ('pg_catalog', 'information_schema'); -- 排除系统对象

说明 #

• 正则表达式会匹配LEFT JOIN、INNER JOIN等所有JOIN类型，以及对应的表名（含schema前缀）
• 先移除注释是为了避免匹配到注释里的JOIN关键字
• 缺点：无法处理嵌套子查询、动态SQL拼接的JOIN，以及带双引号的特殊表名（可调整正则适配）

方案2：编写PL/pgSQL脚本深度解析 #

如果需要更精准的解析（比如处理复杂SQL结构），可以写一个自定义函数，结合正则和SQL逻辑过滤无效匹配。

示例解析函数 #

CREATE OR REPLACE FUNCTION extract_join_details(p_function_oid oid)
RETURNS TABLE(
    schema_name text,
    function_name text,
    join_type text,
    table_schema text,
    table_name text
) AS $$
DECLARE
    v_def text;
    v_clean_def text;
    v_regex text := '(LEFT|RIGHT|INNER|FULL|CROSS)?\s*JOIN\s+(?:([^\s".]+)\.)?([^\s",]+)';
    v_match text[];
BEGIN
    -- 获取函数完整定义并清理注释
    v_def := pg_get_functiondef(p_function_oid);
    v_clean_def := regexp_replace(v_def, '--.*$', '', 'g');
    v_clean_def := regexp_replace(v_clean_def, '/\*.*?\*/', '', 'gms');

    -- 遍历所有匹配的JOIN项
    FOR v_match IN SELECT regexp_matches(v_clean_def, v_regex, 'gi') LOOP
        RETURN QUERY
        SELECT
            n.nspname,
            p.proname,
            COALESCE(v_match[1], 'INNER')::text,
            COALESCE(v_match[2], current_schema())::text,
            v_match[3]::text
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE p.oid = p_function_oid;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 批量调用方式
SELECT * FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind IN ('f', 'p')
    AND n.nspname NOT IN ('pg_catalog', 'information_schema')
LATERAL JOIN extract_join_details(p.oid);

说明 #

• 支持自动填充默认schema（如果JOIN的表没有指定schema）
• 可以扩展正则规则，比如处理带双引号的表名（调整正则为(LEFT|RIGHT|INNER|FULL|CROSS)?\s*JOIN\s+(?:(["][^"]+["]|[^\s".]+)\.)?(["][^"]+["]|[^\s",]+)）

方案3：通过执行计划获取实际执行的JOIN #

如果函数/存储过程已经被执行过，可以通过生成执行计划来提取真实的JOIN关联（包括动态SQL中的JOIN）。

示例查询 #

-- 先创建获取函数执行计划的辅助函数
CREATE OR REPLACE FUNCTION get_function_plan(p_oid oid) RETURNS text AS $$
DECLARE
    v_plan text;
BEGIN
    -- 注意：如果函数有参数，需要调整为适配参数的调用方式
    EXECUTE 'EXPLAIN VERBOSE SELECT * FROM ' || p_oid::regproc INTO v_plan;
    RETURN v_plan;
END;
$$ LANGUAGE plpgsql;

-- 提取JOIN信息
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    regexp_matches(get_function_plan(p.oid), 'Hash Join|Nested Loop|Merge Join', 'gi') AS join_algorithm,
    regexp_matches(get_function_plan(p.oid), 'Relation "(.*?)\."(.*?)"', 'gi') AS joined_tables
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
JOIN
    pg_stat_user_functions s ON p.oid = s.funcid -- 只查询已执行过的函数
WHERE
    p.prokind IN ('f', 'p')
    AND n.nspname NOT IN ('pg_catalog', 'information_schema');

说明 #

• 优点：能捕获动态SQL拼接的JOIN，以及实际执行中用到的表关联
• 缺点：仅对已执行过的函数有效，带参数的函数需要手动调整辅助函数的调用语句传递参数

注意事项 #

1. 对于用Python、Java等外部语言编写的函数，pg_get_functiondef()无法获取函数内的SQL逻辑，只能通过执行计划或者查看源代码解析
2. 如果函数是加密的（创建时带WITH ENCRYPTION），无法获取函数定义，只能依赖执行计划
3. 复杂嵌套子查询中的JOIN可能需要更复杂的语法解析工具（比如自定义递归解析逻辑）

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