PostgreSQL递归SQL函数返回数组含多余元素，求正确实现方案

1. 正确函数实现及原函数失败原因 #

正确实现（PL/pgSQL版本） #

SQL递归函数处理状态维护容易出错，推荐用PL/pgSQL编写循环逻辑，更直观且不易出错：

CREATE OR REPLACE FUNCTION calculate_runs(input int[])
RETURNS int[] AS $$
DECLARE
    result int[] := '{}';
    accumulator int := 0;
    elem int;
BEGIN
    FOREACH elem IN ARRAY input LOOP
        IF elem = 0 THEN
            accumulator := 0;
            result := array_append(result, 0);
        ELSE
            accumulator := accumulator + elem;
            result := array_append(result, accumulator);
        END IF;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

测试验证：

SELECT calculate_runs('{0,1,1,1,1,0,-1,-1,0}');
-- 返回结果：{0,1,2,3,4,0,-1,-2,0}，符合预期

原函数失败的原因 #

1. 数组索引错误：PostgreSQL数组默认从1开始索引，当output为空数组时，cardinality(output)返回0，output[0]是无效索引，返回NULL。处理第一个非0元素时，NULL + 元素值结果仍为NULL，导致后续累加逻辑混乱。
2. 递归状态维护错误：原函数依赖output数组的最后一个值作为累加器，但未单独维护累加状态。遇到0时虽然append了0，但后续递归中若出现索引引用错误，会导致错误的累加值被重复添加，最终输出数组长度超出输入。

2. 无需数组的实现方案 #

如果输入来自array_agg()，说明原始数据是行级数据，完全可以跳过数组，直接用窗口函数处理后插入目标表，避免递归和数组操作的限制：

假设原始数据存储在表source_table中，包含id（排序用）和val（值为0/1/-1）列，目标表target_table有result_val列，实现代码如下：

INSERT INTO target_table (result_val)
SELECT
    CASE
        WHEN val = 0 THEN 0
        ELSE SUM(CASE WHEN val != 0 THEN val ELSE 0 END) OVER (PARTITION BY grp ORDER BY id)
    END AS result_val
FROM (
    -- 按0分割分组：每遇到一个0，分组号递增，将连续非零值归为同一组
    SELECT
        id,
        val,
        SUM(CASE WHEN val = 0 THEN 1 ELSE 0 END) OVER (ORDER BY id) AS grp
    FROM source_table
) t;

原函数输出多余元素的根本原因 #

原函数使用SQL递归时，由于output[cardinality(output)]在空数组时返回NULL，导致CASE分支中出现无效的数值运算（NULL与整数相加），加上SQL函数的集合特性，递归过程中可能产生多个中间数组结果，最终被意外合并成一个更长的数组。此外，递归逻辑未正确维护独立的累加状态，依赖数组元素的索引引用进一步加剧了错误。

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