Explicit type PostgreSQL-CodePudding

I have a problem. There is a function, that counts total budget of department, including departments lower down the hierarchy:

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("BUDGET") FROM department WHERE head_dept = dno INTO cnt;

    IF cnt = 0 
    THEN RETURN QUERY SELECT "BUDGET" FROM department WHERE dept_no = dno;
    END IF;
    SELECT
        ARRAY_AGG(dept_no)
    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;

END; $DEPT_BUDGET$ LANGUAGE plpgsql;

The dept_no has bpchar(3) type. When I'm trying to call a function SELECT public.dept_budget('000'::VARCHAR); , I got an error:

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.

When I change in-type parameter on bpchar or char, I got another error:

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

I don't understand, why forced typization doesn't work. What should I do? Types of data

UPD: Yeah, there is bpchar, but I have already tried to change everywhere VARCHAR(3) on BPCHAR(3), and there is still an error.

CodePudding user response：

This function could be written in sql instead of plpgsql. Try this :

CREATE OR REPLACE FUNCTION PUBLIC.DEPT_BUDGET (DNO VARCHAR)
RETURNS DECIMAL(12,2) LANGUAGE sql AS $$
    SELECT sum( CASE
                  WHEN dept_no = dno
                  THEN budget
                  ELSE dept_budget(dept_no)
                END
              ) :: decimal(12,2)
      FROM department 
     WHERE dept_no = dno
        OR head_dept = dno ;
$$ ;