PostgreSQL递归部门预算函数调用报错：类型不匹配与FOREACH空值问题

一、参数类型不匹配错误解决 #

SQL Error [42883]: ERROR: function dept_budget(integer) does not exist. No function matches the given name and argument types. You might need to add explicit type casts.

报错根源是调用时传入的参数类型与函数定义的bpchar(3)不匹配，PostgreSQL的隐式转换逻辑将输入识别为整数类型，导致找不到对应函数。

解决方式：调用时显式将参数转为bpchar(3)类型：

-- 方式1：使用::类型转换符
SELECT public.dept_budget('000'::bpchar(3));

-- 方式2：使用CAST函数
SELECT public.dept_budget(CAST('000' AS bpchar(3)));

注意：bpchar是定长字符类型，若输入字符串长度不足3，会自动补空格，需确保参数与dept_no字段的存储格式完全一致。

二、FOREACH表达式为空错误解决 #

SQL Error [22004]: ERROR: FOREACH expression must not be null.

报错原因是当当前部门没有子部门时，ARRAY_AGG(dept_no)会返回null，而FOREACH语句无法遍历null数组。

解决方式：用COALESCE函数将null数组替换为空数组，修改获取子部门数组的SQL语句：

SELECT COALESCE(ARRAY_AGG(dept_no), '{}'::bpchar(3)[])
FROM department
WHERE head_dept = dno
INTO rdno;

三、修复函数返回逻辑缺失问题 #

原函数仅在无子女部门时返回结果，有子部门时累加完总预算后没有返回语句，导致无输出。需在循环结束后添加返回总预算的语句，同时优化无子女部门的分支，提前退出避免冗余代码。

完整修正后的函数代码 #

CREATE OR REPLACE FUNCTION PUBLIC.DEPT_BUDGET (DNO BPCHAR(3))
RETURNS TABLE (
    TOT DECIMAL(12,2)
)
AS $DEPT_BUDGET$
DECLARE sumb DECIMAL(12, 2);
DECLARE rdno BPCHAR(3)[];
DECLARE cnt INTEGER;
DECLARE I BPCHAR(3);
BEGIN
    tot = 0;

    -- 获取当前部门预算
    SELECT "BUDGET" FROM department WHERE dept_no = dno INTO tot;

    -- 获取子部门数量
    SELECT count(*) FROM department WHERE head_dept = dno INTO cnt;

    -- 无子女部门时直接返回当前预算并退出
    IF cnt = 0 
    THEN 
        RETURN QUERY SELECT tot;
        RETURN;
    END IF;

    -- 获取子部门编号数组，无数据时返回空数组
    SELECT COALESCE(ARRAY_AGG(dept_no), '{}'::bpchar(3)[])
    FROM department
    WHERE head_dept = dno
    INTO rdno;
    
    -- 递归累加子部门总预算
    FOREACH I IN ARRAY rdno
    LOOP
        SELECT * FROM DEPT_BUDGET(I) INTO SUMB;
        tot = tot + sumb;
    END LOOP;

    -- 返回最终总预算
    RETURN QUERY SELECT tot;

END; $DEPT_BUDGET$ LANGUAGE plpgsql;

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