创建PostgreSQL薪资平均值聚合函数时遇函数不存在错误求助

错误原因 #

• 参数类型不匹配：你定义的avg_final_func接收numeric[]类型参数，但聚合函数的STYPE设置为numeric，PostgreSQL会尝试调用接收numeric类型的avg_final_func，但你并没有定义这个版本的函数，因此触发42883错误。
• 聚合逻辑缺陷：仅通过累加薪资总和无法计算平均值，还需要记录参与计算的员工数量，你的原设计缺少计数逻辑，即使解决类型问题也无法得到正确结果。

解决方法 #

需要调整聚合函数的中间状态存储，同时修正函数参数类型匹配问题：

1. 创建复合类型存储中间状态

CREATE TYPE salary_state AS (sum numeric, count integer);

该类型用于同时保存累加的薪资总和与员工计数。

2. 定义累加函数（SFUNC）

CREATE OR REPLACE FUNCTION salary_accumulate(salary_state, numeric)
RETURNS salary_state
AS $$
SELECT ($1.sum + $2, $1.count + 1)::salary_state;
$$ LANGUAGE SQL;

每次调用时，将当前薪资累加到总和中，并将计数加1。

3. 定义最终计算函数（FINALFUNC）

CREATE OR REPLACE FUNCTION salary_final(salary_state)
RETURNS numeric
AS $$
SELECT CASE WHEN $1.count = 0 THEN NULL ELSE $1.sum / $1.count END;
$$ LANGUAGE SQL;

通过总和除以计数得到平均值，同时处理计数为0的边界情况（避免除以0错误）。

4. 创建聚合函数

CREATE AGGREGATE avg_salary(numeric) (
  SFUNC = salary_accumulate,
  STYPE = salary_state,
  FINALFUNC = salary_final,
  INITCOND = '(0, 0)'
);

这里STYPE设置为我们创建的复合类型，INITCOND初始化总和为0、计数为0。

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