PostgreSQL公共模式下批量查询含projects_前缀表所有记录方法

你当前的SQL仅查询了符合条件的表的元数据（比如表名、所属schema等），并没有获取表中的实际业务数据。要实现无需逐个指定表名、查询所有projects_前缀表的全部记录，需要借助动态SQL生成并执行跨表查询语句，具体方案如下：

方案1：生成并手动执行合并查询语句 #

先通过SQL生成包含所有目标表的UNION ALL合并语句，再执行该语句：

-- 生成合并查询语句
SELECT string_agg('SELECT * FROM public.' || quote_ident(table_name), ' UNION ALL ')
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name LIKE 'projects_%';

执行上述语句后，会得到类似以下的结果：

SELECT * FROM public.projects_2019 UNION ALL SELECT * FROM public.projects_2020 UNION ALL SELECT * FROM public.projects_2021

直接运行这个生成的语句，就能获取所有目标表的全部记录。

方案2：创建函数自动执行（推荐） #

如果需要频繁执行这类查询，可以创建一个PL/pgSQL函数，自动生成并执行动态SQL：

-- 创建函数，返回任意一个目标表的结构（这里用projects_2019为例）
CREATE OR REPLACE FUNCTION get_all_projects()
RETURNS SETOF projects_2019
LANGUAGE plpgsql AS $$
DECLARE
    sql_text text;
BEGIN
    -- 拼接所有目标表的查询语句
    SELECT string_agg('SELECT * FROM public.' || quote_ident(table_name), ' UNION ALL ')
    INTO sql_text
    FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name LIKE 'projects_%';

    -- 执行动态SQL并返回结果
    RETURN QUERY EXECUTE sql_text;
END;
$$;

-- 调用函数获取所有数据
SELECT * FROM get_all_projects();

方案3：psql命令行一键执行 #

如果使用psql客户端，可以直接用\gexec命令自动执行生成的SQL：

SELECT string_agg('SELECT * FROM public.' || quote_ident(table_name), ' UNION ALL ')
FROM information_schema.tables
WHERE table_schema = 'public' AND table_name LIKE 'projects_%'
\gexec

注意事项 #

• 所有projects_前缀表的列结构必须完全一致（列名、数据类型、顺序都要匹配），否则UNION ALL会报错。
• 使用quote_ident函数可以避免表名包含特殊字符时出现SQL语法错误，同时防止SQL注入风险。
• 如果目标表数据量较大，合并查询可能会消耗较多数据库资源，建议按需添加过滤条件（比如在生成的SELECT语句中加入WHERE子句）。

内容的提问来源于stack exchange，提问作者Lucien S.