PostgreSQL遍历多schema统计benches总数的循环查询方案问询

原函数的问题 #

• 函数命名语义不符：all_customers_dynamic与统计长椅总数的功能完全不相关
• 返回类型错误：SETOF classroom指定返回classroom表的行结构，但实际需要返回的是数值计数结果
• 变量名使用保留字：schema是PostgreSQL的保留关键字，用作变量名可能引发语法冲突
• 未实现总数统计：原函数仅返回每个教室的独立长椅数，未完成累加得到总和的需求

正确实现方案 #

方案1：返回每个教室的长椅数量（含教室名称） #

如果需要先查看各教室的具体计数，再自行求和，可使用此函数：

CREATE OR REPLACE FUNCTION get_classroom_bench_counts() 
RETURNS TABLE(classroom_name text, bench_count bigint) AS $$
DECLARE
    classroom_rec RECORD;
BEGIN
    FOR classroom_rec IN SELECT classroom_name FROM public.classroom LOOP
        RETURN QUERY EXECUTE format(
            'SELECT %L::text, COUNT(DISTINCT id) FROM %I.benches',
            classroom_rec.classroom_name,
            classroom_rec.classroom_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT * FROM get_classroom_bench_counts();

方案2：直接返回所有教室的长椅总数 #

如果只需要最终的累计总数，可在函数内部完成累加计算：

CREATE OR REPLACE FUNCTION get_total_benches() 
RETURNS bigint AS $$
DECLARE
    classroom_rec RECORD;
    total bigint := 0;
    current_count bigint;
BEGIN
    FOR classroom_rec IN SELECT classroom_name FROM public.classroom LOOP
        EXECUTE format(
            'SELECT COUNT(DISTINCT id) FROM %I.benches',
            classroom_rec.classroom_name
        ) INTO current_count;
        total := total + current_count;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

调用方式：

SELECT get_total_benches();

额外优化：无PL/pgSQL的纯SQL实现 #

若不想使用存储过程，可通过动态SQL结合UNION ALL实现，执行效率更优：

WITH schema_queries AS (
    SELECT format(
        'SELECT COUNT(DISTINCT id) FROM %I.benches',
        classroom_name
    ) AS query
    FROM public.classroom
)
SELECT SUM(count) AS total_benches
FROM (
    SELECT * FROM schema_queries, LATERAL (EXECUTE query) AS t(count)
) AS all_counts;

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