PL/pgSQL动态查询执行报错，求解决及SQL替代方案

一、PL/pgSQL块报错原因及修正 #

你的报错大概率是动态SQL拼接时未正确处理表标识符（尤其是带schema的表名），或是没有正确捕获查询结果。以下是修正后的PL/pgSQL块，可遍历指定schema下的表，执行空闲空间查询并输出结果：

DO $$
DECLARE
    rec record;
    v_table regclass;
BEGIN
    RAISE NOTICE 'check1';
    -- 遍历public schema下的所有普通表，可按需修改schema或添加过滤条件
    FOR v_table IN
        SELECT relname::regclass
        FROM pg_class
        WHERE relkind = 'r' 
          AND relnamespace = 'public'::regnamespace
    LOOP
        RAISE NOTICE 'for table %', v_table;
        -- 用%I正确转义表名，避免特殊字符/schema导致的语法错误
        EXECUTE format('
            SELECT %L as table_name,
                   count(*) as "number of pages",
                   pg_size_pretty(cast(avg(avail) as bigint)) as "Av. freespace size",
                   round(100 * avg(avail)/8192 ,2) as "Av. freespace ratio"
            FROM pg_freespace(%I)', v_table, v_table)
        INTO rec;
        -- 输出当前表的统计结果
        RAISE NOTICE 'Result: %', rec;
    END LOOP;
END $$;

关键修正点： #

• 使用%I格式符处理表名，自动添加符合PostgreSQL规范的引号，适配带特殊字符或schema的表名
• 用%L将表名转为字符串常量，方便在结果中标识对应表
• 通过INTO捕获动态SQL的执行结果，确保逻辑完整

二、纯SQL实现方案 #

虽然pg_freespace是流水线函数，但可以结合pg_class和LATERAL查询实现纯SQL批量统计，核心是为每个表单独调用pg_freespace并聚合结果：

SELECT 
    c.relname AS table_name,
    count(*) as "number of pages",
    pg_size_pretty(cast(avg(f.avail) as bigint)) as "Av. freespace size",
    round(100 * avg(f.avail)/8192 ,2) as "Av. freespace ratio"
FROM pg_class c
JOIN LATERAL pg_freespace(c.relname::regclass) f ON true
WHERE c.relkind = 'r'  -- 仅查询普通表
  AND c.relnamespace = 'public'::regnamespace  -- 指定目标schema
GROUP BY c.relname
ORDER BY "Av. freespace ratio" DESC;

说明： #

• 通过pg_class获取目标表的元数据，将relname转为regclass类型，匹配pg_freespace的参数要求
• LATERAL关键字让pg_freespace为每个表单独执行，返回该表的空闲空间记录
• 按表分组聚合，得到每张表的空闲空间统计结果

常见问题排查 #

1. 若纯SQL提示pg_freespace不存在：

   • 确认PostgreSQL版本≥12（pg_freespace是12版本新增函数）
   • 检查当前用户是否有目标表的访问权限

2. PL/pgSQL仍报错：

   • 单独执行SELECT pg_freespace('public.目标表名')验证参数有效性
   • 确认表名无非法字符，%I已自动处理引号转义

内容的提问来源于stack exchange，提问作者Umut TEKİN