Принудительная типизация PostgreSQL

Question

Возникла проблема. Есть такая функция (подсчитывающая общий бюджет, включая отделы ниже):

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;

При попытке запустить ее возникает ошибка:

SELECT public.dept_budget('000'::VARCHAR);
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.

Если поменять тип входного параметра, чтобы функция в целом запускалась, возникает ошибка следующая:

SQL Error [22004]: ERROR: FOREACH expression must not be null
  Где: PL/pgSQL function dept_budget(character) line 25 at FOREACH over array

Принудительная типизация

WHERE
        head_dept = cast(dno as varchar)

не срабатывает, и результат SELECT'а равен NULL. Почитала, что Postgres в принципе автоматически конвертирует char в bpchar, и "лучшее" решение -- не использовать char вовсе. Подскажите, пожалуйста, как исправить ошибки, чтобы функция запускалась? Какой тип где поставить?

Answer 1

Твой подход в принципе неправильный - ты используешь Постгрис как императивный язык, а он база данных и у него есть мощные инструменты по обработке данных.

Из того, что я понял, задача такая - посчитать общий бюджет отдела и его подотделов. Для этого надо использовать иерархические запросы

with recursive hierarchy(dept_no, head_dept, BUDGET) as (
  select department.dept_no, department.head_dept, department.BUDGET 
  from department 
  where dept_no = dno -- Входной параметр
  
  union all
  
  select department.dept_no, department.head_dept, department.BUDGET 
  from department , hierarchy 
  where department.head_dept = hierarchy.dept_no 
)
select sum(BUDGET) from hierarchy

БЛОГ НА HUSL

Принудительная типизация PostgreSQL

Ответы (1 шт):