如何在PostgreSQL中基于参照完整性约束自动生成左外连接查询？

有没有人写过PL/pgSQL或者类似方法，仅依靠外键约束数据自动生成多层左外连接查询？这类查询的生成往往耗时，且大多是重复机械的工作。

示例表结构 #

create table aaa ( id bigint, name varchar);
create table aaa_option( id bigint,aaa_id bigint, name varchar, value varchar);
create table aaa_sub_option( id bigint,option_id bigint, name varchar, value varchar);

CREATE UNIQUE INDEX on aaa(id) ;
CREATE UNIQUE INDEX ON aaa_sub_option(id) ;

ALTER TABLE aaa_option ADD CONSTRAINT aaa_const FOREIGN KEY (aaa_id) REFERENCES aaa(id);
ALTER TABLE aaa_sub_option ADD CONSTRAINT aaa_sub_const FOREIGN KEY (option_id) REFERENCES aaa_option(id);

期望功能 #

希望实现一个名为rquery的函数，调用方式及返回结果如下：

调用rquery('aaa', 1) #

返回：

select aaa.id as aaa_X_id, aaa.name as aaa_X_name from aaa;

调用rquery('aaa', 2) #

返回：

select aaa.id as aaa_X_id, aaa.name as aaa_X_name,
    X1.id as aaa_option_X_id, X1.aaa_id as aaa_option_X_aaa_id, X1.name as aaa_option_X_name,X1.value as aaa_option_X_value 
from aaa
left outer join aaa_option as X1 on aaa.id=X1.aaa_id;

调用rquery('aaa', 3) #

返回包含三层关联的左外连接查询（关联aaa→aaa_option→aaa_sub_option）。

实现方案 #

以下是一个可行的PL/pgSQL函数实现，它会递归遍历外键约束，生成对应层级的左外连接查询：

CREATE OR REPLACE FUNCTION rquery(p_table_name text, p_depth integer)
RETURNS text AS $$
DECLARE
    v_query text;
    v_from_clause text;
    v_select_clause text;
    v_new_select text;
    v_rec record;
    v_alias_prefix text := 'X';
    v_current_depth integer := 1;
    v_tables jsonb := jsonb_build_array(jsonb_build_object('table', p_table_name, 'alias', p_table_name, 'parent_col', NULL, 'child_col', NULL));
    v_new_tables jsonb;
BEGIN
    -- 生成初始SELECT子句
    SELECT string_agg(format('%I.%I as %s_X_%s', t.alias, c.column_name, t.table, c.column_name), ', ')
    INTO v_select_clause
    FROM jsonb_to_recordset(v_tables) AS t(table text, alias text, parent_col text, child_col text)
    JOIN information_schema.columns c ON c.table_name = t.table;

    v_from_clause := format('%I as %I', p_table_name, p_table_name);

    WHILE v_current_depth < p_depth LOOP
        v_new_tables := '[]'::jsonb;
        v_new_select := '';

        -- 遍历当前层级的所有表，查找其关联的子表（通过外键）
        FOR v_rec IN SELECT * FROM jsonb_to_recordset(v_tables) AS t(table text, alias text, parent_col text, child_col text) LOOP
            SELECT jsonb_agg(jsonb_build_object(
                'table', tc.table_name,
                'alias', format('%s%s', v_alias_prefix, v_current_depth),
                'parent_col', kcu.column_name,
                'child_col', ccu.column_name
            ))
            INTO v_new_tables
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu 
                ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage ccu 
                ON tc.constraint_name = ccu.constraint_name
            WHERE tc.constraint_type = 'FOREIGN KEY'
              AND ccu.table_name = v_rec.table
              AND ccu.column_name = 'id'; -- 假设父表主键为id

            IF v_new_tables IS NOT NULL AND v_new_tables != '[]'::jsonb THEN
                -- 添加JOIN子句
                v_from_clause := format('%s
left outer join %I as %s on %I.%I = %s.%I',
                    v_from_clause,
                    (v_new_tables->0->>'table')::text,
                    (v_new_tables->0->>'alias')::text,
                    v_rec.alias,
                    (v_new_tables->0->>'child_col')::text,
                    (v_new_tables->0->>'alias')::text,
                    (v_new_tables->0->>'parent_col')::text
                );

                -- 添加SELECT子句字段
                SELECT string_agg(format('%I.%I as %s_X_%s', t.alias, c.column_name, t.table, c.column_name), ', ')
                INTO v_new_select
                FROM jsonb_to_recordset(v_new_tables) AS t(table text, alias text, parent_col text, child_col text)
                JOIN information_schema.columns c ON c.table_name = t.table;

                v_select_clause := format('%s,
    %s', v_select_clause, v_new_select);
            END IF;
        END LOOP;

        v_tables := v_new_tables;
        v_current_depth := v_current_depth + 1;
    END LOOP;

    v_query := format('select %s from %s;', v_select_clause, v_from_clause);
    RETURN v_query;
END;
$$ LANGUAGE plpgsql;

说明 #

• 该函数通过查询information_schema中的外键约束信息，递归遍历关联表
• 假设每个表的主键字段名为id，如果你的表主键命名规则不同，需要调整代码中ccu.column_name = 'id'的对应部分
• 生成的字段别名遵循表名_X_字段名的格式，避免字段名冲突
• 仅处理直接关联的外键关系，逐层生成左外连接

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