PostgreSQL普通自定义聚合函数如何传递直接参数？

你遇到的报错核心原因是：PostgreSQL 10及更早版本中，普通聚合函数的FINALFUNC只能接收状态参数（也就是STYPE定义的类型），无法直接获取你在聚合函数中声明的额外参数。你定义的lastNavg_finalfn需要两个参数，但PostgreSQL只会传递状态数组给它，所以才会提示找不到只接受integer[]的函数。

下面给你两种可行的解决方案：

方案1：将额外参数嵌入状态数组 #

把参数n存到状态数组的第一个位置，后续元素存储数据。这样转换函数和最终函数都能从状态数组里拿到n的值：

修改后的函数和聚合定义 #

-- 转换函数：第一个元素存n，后面追加数据
CREATE FUNCTION lastNavg_transfn(state integer[], next integer, n integer) RETURNS integer[] AS $$
BEGIN
    -- 如果是初始空数组，先把n放进去
    IF array_length(state, 1) IS NULL THEN
        RETURN array[n, next];
    END IF;
    RETURN array_append(state, next);
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

-- 最终函数：从状态数组第一个元素取n，计算最近n个值的平均
CREATE FUNCTION lastNavg_finalfn(state integer[]) RETURNS float AS $$
DECLARE
    n integer := state[1];
    card integer;
    count float;
    sum float;
BEGIN
    count := 0;
    sum := 0;
    card := array_length(state, 1);
    -- 注意数组长度要减1（因为第一个元素是n）
    FOR i IN greatest(2, card - n + 1)..card LOOP
        sum := sum + state[i];
        count := count + 1;
    END LOOP;
    RETURN sum/count;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE AGGREGATE lastNavg(integer, integer) (
    SFUNC = lastNavg_transfn,
    STYPE = integer[],
    INITCOND = '{}',
    FINALFUNC = lastNavg_finalfn,
    PARALLEL = SAFE
);

方案2：使用自定义复合类型作为状态（更清晰） #

如果觉得数组里混存参数和数据不够直观，可以定义一个复合类型来存储状态，把n和数据数组分开：

步骤1：创建复合类型 #

CREATE TYPE lastNavg_state AS (
    window_size integer,
    values integer[]
);

步骤2：定义转换函数和最终函数 #

CREATE FUNCTION lastNavg_transfn(state lastNavg_state, next integer, n integer) RETURNS lastNavg_state AS $$
BEGIN
    -- 初始状态时初始化window_size和values数组
    IF state.window_size IS NULL THEN
        RETURN (n, ARRAY[next])::lastNavg_state;
    END IF;
    RETURN (state.window_size, array_append(state.values, next))::lastNavg_state;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE FUNCTION lastNavg_finalfn(state lastNavg_state) RETURNS float AS $$
DECLARE
    n integer := state.window_size;
    card integer;
    count float;
    sum float;
BEGIN
    count := 0;
    sum := 0;
    card := array_length(state.values, 1);
    FOR i IN greatest(1, card - n + 1)..card LOOP
        sum := sum + state.values[i];
        count := count + 1;
    END LOOP;
    RETURN sum/count;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

步骤3：创建聚合函数 #

CREATE AGGREGATE lastNavg(integer, integer) (
    SFUNC = lastNavg_transfn,
    STYPE = lastNavg_state,
    INITCOND = '(NULL, {})',
    FINALFUNC = lastNavg_finalfn,
    PARALLEL = SAFE
);

注意事项 #

• 这两种方案都兼容PostgreSQL 10的限制，因为它们都把额外参数整合到了STYPE定义的状态中，让FINALFUNC可以通过状态获取到参数值。
• 如果你的实际需求是窗口函数（累积最小值），其实可以考虑用RANGE或ROWS窗口框架结合内置的min()函数，但如果是自定义数据类型的特殊处理，上面的方法依然适用。

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