如何在PostgreSQL中跨可变Schema关联查询合并结果集？

在PostgreSQL里要实现你要的效果，得用动态SQL处理不同Schema下的表关联，下面是具体实现方式：

方法一：创建PL/pgSQL函数（推荐） #

先写一个函数，自动遍历所有目标Schema，把项目信息和对应Schema下的设备数据合并返回：

CREATE OR REPLACE FUNCTION get_combined_project_devs()
RETURNS TABLE(
    project_id INT,
    project_name VARCHAR,
    schema_p VARCHAR,
    dev_id INT,
    dev_name VARCHAR
) AS $$
DECLARE
    project_rec RECORD;
BEGIN
    -- 先获取所有项目和对应的Schema信息
    FOR project_rec IN 
        SELECT project_id, project_name, schema_p
        FROM public.projects
        JOIN public.schema_ps ON public.schema_ps.root_id = public.projects.project_id
    LOOP
        -- 动态执行每个Schema下的com_set查询，关联项目信息后返回
        RETURN QUERY EXECUTE format(
            'SELECT $1, $2, $3, dev_id, dev_name FROM %I.com_set',
            project_rec.schema_p
        ) USING project_rec.project_id, project_rec.project_name, project_rec.schema_p;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

直接查询这个函数就能得到合并后的完整结果集：

SELECT * FROM get_combined_project_devs() ORDER BY project_id ASC;

关键细节 #

• format()函数里的%I占位符会自动给Schema名称添加正确的引号，避免特殊字符、关键字引发的报错，同时防范SQL注入风险。
• USING子句用于传递项目参数，无需直接拼接字符串，更安全可靠。

方法二：动态生成UNION ALL语句（临时场景使用） #

如果不想创建函数，可以通过字符串拼接生成所有Schema的查询语句再执行：

DO $$
DECLARE
    dyn_sql TEXT;
BEGIN
    SELECT string_agg(
        format(
            'SELECT p.project_id, p.project_name, s.schema_p, c.dev_id, c.dev_name 
             FROM public.projects p 
             JOIN public.schema_ps s ON s.root_id = p.project_id 
             JOIN %I.com_set c ON 1=1 
             WHERE s.schema_p = %L',
            schema_p, schema_p
        ),
        ' UNION ALL '
    ) INTO dyn_sql
    FROM (SELECT DISTINCT schema_p FROM public.schema_ps) AS s;

    EXECUTE dyn_sql;
END $$;

注：这种方式执行后结果不会直接返回，如需获取结果集，还是推荐使用函数方式。

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