PostgreSQL如何批量查询public schema下各表最新version值

结论 #

PostgreSQL 无法用纯静态SQL直接实现这个需求——因为你需要查询的目标表名是从元数据动态获取的，标准SQL不支持在单次查询中动态遍历未知表名执行查询，不需要手动写多表关联，用动态SQL方案即可实现，以下是两种可直接用的实现方式：

方案1：生成可直接执行的UNION ALL查询（最轻量，无需创建对象） #

直接执行以下语句，会自动拼接出所有符合条件的表的查询逻辑：

SELECT string_agg(
    format('SELECT %L AS table_name, max(version) AS "Version" FROM public.%I', table_name, table_name),
    ' UNION ALL '
) AS generated_query
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
  -- 过滤掉不存在version列的表，避免执行报错
  AND EXISTS (
      SELECT 1
      FROM information_schema.columns c
      WHERE c.table_schema = tables.table_schema
        AND c.table_name = tables.table_name
        AND c.column_name = 'version'
  );

执行后会得到一列generated_query，内容是完整的UNION ALL查询语句，把该列的结果复制出来直接执行，就能得到你期望的「表名+最新version值」两列结果。

补充：这里用max(version)替代你原来写的order by version desc limit 1，如果version列建有索引，max的查询效率远高于全表排序取第一条；如果你的version是单调递增的版本号，结果和排序取第一条完全一致。

方案2：匿名块直接输出结果（无需复制拼接） #

如果不想手动复制生成的语句，可以直接执行PL/pgSQL匿名块，自动遍历表并返回结果：

DO $$
DECLARE
    tbl record;
    execute_sql text;
BEGIN
    -- 创建临时表存储查询结果，事务结束后自动删除
    CREATE TEMP TABLE IF NOT EXISTS tmp_version_result (
        table_name text,
        "Version" bigint
    ) ON COMMIT DROP;

    -- 遍历public下所有带version列的业务表
    FOR tbl IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_type = 'BASE TABLE'
          AND EXISTS (
              SELECT 1
              FROM information_schema.columns c
              WHERE c.table_schema = tables.table_schema
                AND c.table_name = tables.table_name
                AND c.column_name = 'version'
          )
    LOOP
        execute_sql := format(
            'INSERT INTO tmp_version_result SELECT %L, max(version) FROM public.%I',
            tbl.table_name,
            tbl.table_name
        );
        EXECUTE execute_sql;
    END LOOP;
END $$;

-- 查询临时表获取最终结果
SELECT * FROM tmp_version_result;

注意事项 #

• 两种方案都默认过滤了public下的视图、系统表，只查询用户自己创建的普通业务表
• 如果部分表的version列存在空值，max会自动忽略空值，不影响结果
• 如果需要排除特定表，直接在查询information_schema.tables的WHERE条件里加过滤规则即可
• 如果version字段不是整数类型（比如字符串、时间戳），修改方案2临时表中Version字段的类型即可适配

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