创建PostgreSQL视图时如何使用循环逻辑生成统计报表

你原有写法的核心问题：

• DO 是匿名PL/pgSQL块，没有返回值，无法直接输出查询结果，也不能定义为视图
• 标准SQL的SELECT语句列数必须是静态固定的，不能通过循环动态生成列，普通视图不支持动态列结构
• 代码存在语法错误：CASE判断、循环逻辑不能直接放在SELECT语句外层，也不能直接拼接列别名

根据你的使用场景，可选择以下两种实现方案：

方案1：行式静态输出（推荐） #

不需要动态逻辑，兼容性最高，导出后转透视表也非常方便，适合绝大多数报表场景：

WITH year_range AS (
    SELECT MIN(start_year) AS yr_min, MAX(end_year) AS yr_max 
    FROM schema.table
),
years AS (
    SELECT generate_series(yr_min, yr_max) AS stat_year 
    FROM year_range
)
SELECT
    CASE
        WHEN t.type = 2 THEN 'AAAA'
        WHEN t.type = 1 THEN 'BBBB'
        ELSE 'other'
    END AS type_orp,
    y.stat_year,
    ROUND(SUM(CASE WHEN t.start_year >= y.stat_year AND t.end_year < y.stat_year + 1 THEN t.area ELSE 0 END)) AS tot_area
FROM schema.table t
CROSS JOIN years y
GROUP BY type_orp, stat_year
ORDER BY type_orp, stat_year;

输出格式为每行对应「类型+统计年份+对应面积」，可直接作为视图使用。

方案2：动态列输出 #

如果必须要每一年作为单独的列，需要用PL/pgSQL动态拼接SQL，因为PostgreSQL视图本身要求固定列结构，所以可以通过两种方式实现：

方式A：动态函数返回结果集 #

CREATE OR REPLACE FUNCTION get_yearly_area_stats()
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
DECLARE
    yr_min INTEGER;
    yr_max INTEGER;
    i INTEGER;
    dyn_cols TEXT := '';
    sql_str TEXT;
BEGIN
    -- 获取年份范围
    SELECT MIN(start_year), MAX(end_year) INTO yr_min, yr_max 
    FROM schema.table;
    
    -- 拼接动态年份汇总列
    FOR i IN yr_min..yr_max LOOP
        dyn_cols := dyn_cols || format(', ROUND(SUM(CASE WHEN start_year >= %s AND end_year < %s THEN area END)) AS tot_%s', i, i+1, i);
    END LOOP;
    
    -- 拼接完整查询SQL
    sql_str := format('
        SELECT
            CASE
                WHEN type = 2 THEN ''AAAA''
                WHEN type = 1 THEN ''BBBB''
                ELSE ''other''
            END AS type_orp
            %s
        FROM schema.table
        GROUP BY type_orp
        ORDER BY type_orp
    ', dyn_cols);
    
    -- 执行动态SQL返回结果
    RETURN QUERY EXECUTE sql_str;
END;
$$;

调用时需要指定返回的列结构，示例（假设年份范围是2018-2023）：

SELECT * FROM get_yearly_area_stats() AS (
    type_orp TEXT, 
    tot_2018 NUMERIC, 
    tot_2019 NUMERIC, 
    tot_2020 NUMERIC, 
    tot_2021 NUMERIC, 
    tot_2022 NUMERIC, 
    tot_2023 NUMERIC
);

方式B：动态刷新视图 #

如果要直接用视图查询，可以编写函数动态更新视图定义，年份范围变化时手动刷新即可：

CREATE OR REPLACE FUNCTION refresh_yearly_area_view()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    yr_min INTEGER;
    yr_max INTEGER;
    i INTEGER;
    dyn_cols TEXT := '';
    sql_str TEXT;
BEGIN
    SELECT MIN(start_year), MAX(end_year) INTO yr_min, yr_max 
    FROM schema.table;
    
    FOR i IN yr_min..yr_max LOOP
        dyn_cols := dyn_cols || format(', ROUND(SUM(CASE WHEN start_year >= %s AND end_year < %s THEN area END)) AS tot_%s', i, i+1, i);
    END LOOP;
    
    sql_str := format('
        CREATE OR REPLACE VIEW yearly_area_stats AS
        SELECT
            CASE
                WHEN type = 2 THEN ''AAAA''
                WHEN type = 1 THEN ''BBBB''
                ELSE ''other''
            END AS type_orp
            %s
        FROM schema.table
        GROUP BY type_orp
        ORDER BY type_orp
    ', dyn_cols);
    
    EXECUTE sql_str;
END;
$$;

使用方法：

1. 首次使用或数据中年份范围变化时，执行刷新：SELECT refresh_yearly_area_view();
2. 直接查询视图：SELECT * FROM yearly_area_stats;

注意事项 #

• 动态列方案不适合用在需要固定接口的业务场景，列结构会随表内年份范围变化
• 报表导出场景优先选择方案1，逻辑更简单，兼容性更高

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