PostgreSQL存储过程动态SQL报错：子查询返回多行求助

问题背景 #

作为PostgreSQL新手，我尝试创建存储过程执行数据质量查询，将结果存入schema_name.data_profile_col_sum表，但执行时触发错误：More than one row returned from subquery，需要获取查询返回的所有值。

原存储过程代码 #

CREATE OR REPLACE PROCEDURE schema_name.sp_perform_data_profiling_test()
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    rec_count int := 5000;
    query text;
    rec record;
BEGIN
    query :=  'select '
    || 'count(distinct '|| c.column_name ||'::text) as num_distinct '
    || 'sum(case when '|| c.column_name ||'::text is null then 1 else 0 end) num_nulls ' 
    || 'from ' || t.table_schema || '.' || c.table_name 
    || ' ' || CHR(13) || chr(10) ||' ; ' || chr(13) || chr(10)
    from information_schema.columns c
    inner join information_schema.tables t
    on c.table_name = t.table_name
    and c.table_schema = t.table_schema
    where c.table_schema in ('schema_name')
    and c.table_name like 'table_name_%';

    for rec in execute query using rec_count
    loop
        insert into schema_name.data_profile_col_sum
        (   
            num_distinct, num_nulls
         )
         values
         (
            rec.num_distinct,rec.num_nulls
         );
    raise notice '%',rec;
    end loop;
end;
$BODY$;

调用语句 #

call schema_name.sp_perform_data_profiling_test();

报错信息 #

ERROR:  query "SELECT 'select '
|| 'count(distinct '|| c.column_name ||'::text) as num_distinct '
|| 'from ' || t.table_schema || '.' || c.table_name 
|| ' ' || CHR(13) || chr(10) ||' ; ' || chr(13) || chr(10)
from information_schema.columns c
inner join information_schema.tables t
on c.table_name = t.table_name
and c.table_schema = t.table_schema
where c.table_schema in ('schema_name')
and c.table_name like 'Table_%_name'" returned more than one row
CONTEXT:  PL/pgSQL function schema_name.sp_perform_data_profiling_test() line 18 at assignment
SQL state: 21000

预期结果 #

错误原因 #

原代码试图将多行SQL字符串结果直接赋值给单个text类型的query变量，而PL/pgSQL不允许这种赋值方式——当右侧查询返回多行时，赋值操作会抛出"More than one row returned from subquery"错误。

修正后的存储过程代码 #

CREATE OR REPLACE PROCEDURE schema_name.sp_perform_data_profiling_test()
LANGUAGE plpgsql
AS $BODY$
DECLARE
    query text;
    rec record;
BEGIN
    -- 遍历符合条件的所有字段
    FOR rec IN 
        SELECT c.column_name, t.table_schema, c.table_name
        FROM information_schema.columns c
        JOIN information_schema.tables t
            ON c.table_name = t.table_name 
            AND c.table_schema = t.table_schema
        WHERE c.table_schema = 'schema_name'
          AND c.table_name LIKE 'table_name_%'
    LOOP
        -- 使用format函数安全生成动态SQL，避免标识符转义问题
        query := format(
            'SELECT count(distinct %I::text) AS num_distinct,
                    sum(case when %I::text IS NULL then 1 else 0 end) AS num_nulls
             FROM %I.%I',
            rec.column_name, rec.column_name, rec.table_schema, rec.table_name
        );
        
        -- 执行动态查询并直接插入结果到目标表
        EXECUTE format(
            'INSERT INTO schema_name.data_profile_col_sum (num_distinct, num_nulls)
             %s', query
        );
        
        RAISE NOTICE '已处理字段: %.%', rec.table_name, rec.column_name;
    END LOOP;
END;
$BODY$;

关键修改说明 #

1. 遍历字段而非批量生成SQL：直接循环遍历information_schema中符合条件的字段，为每个字段单独生成查询语句，避免多行结果赋值给单个变量的问题。
2. 使用format()函数：安全处理数据库标识符（表名、字段名），自动转义特殊字符，避免SQL注入风险，比直接字符串拼接更可靠。
3. 简化结果插入：每个字段的查询仅返回一行结果，直接通过EXECUTE执行插入操作，无需额外循环接收结果。

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