Распределение расходов

CREATE TABLE depts(
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
employees INTEGER,
expenses NUMERIC(10,2)
);
INSERT INTO depts(employees) VALUES (10),(10),(10);

CREATE TYPE public.troundfinance AS (
    nerror NUMERIC,
    namount NUMERIC);
    
CREATE OR REPLACE FUNCTION public.roundfinance_final(SELF public.troundfinance)
 RETURNS NUMERIC
 LANGUAGE plpgsql
AS $function$
     BEGIN
       RETURN SELF.nAmount; 
     END;
    $function$
;
 
CREATE OR REPLACE FUNCTION public.roundfinance_transition(SELF public.troundfinance, npvalue NUMERIC, npaccuracy NUMERIC DEFAULT 2)
 RETURNS public.troundfinance
 LANGUAGE plpgsql
AS $function$
     BEGIN
       IF npValue IS NOT NULL THEN
         SELF.nError := SELF.nError + npValue - round(npValue,npAccuracy);
         SELF.nAmount := round(npValue,npAccuracy) + round(SELF.nError,npAccuracy);
         SELF.nError := SELF.nError - round(SELF.nError,npAccuracy);   
       END IF;
       RETURN SELF;
     END;
    $function$
;
 
CREATE OR REPLACE AGGREGATE public.roundfinance(NUMERIC,NUMERIC) (
    SFUNC = public.roundfinance_transition,
    STYPE = public.troundfinance,
    FINALFUNC = public.roundfinance_final,
    FINALFUNC_MODIFY = READ_ONLY,
    INITCOND = '(0,0)'
);

UPDATE depts
SET expenses = t.expenses
FROM 
 (
  SELECT 
   id,
   roundfinance(:nTotalExpenses * employeesTotal / employees, 2) OVER (ORDER BY sign(employees), id) AS expenses 
  FROM
   (
    SELECT 
      id,
      employees,
      SUM(employees) OVER () employeesTotal
    FROM depts
   ) t
 ) t
WHERE 
  depts.id = t.id

CREATE TABLE depts(
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
employees INTEGER,
expenses NUMERIC(10,2)
);
INSERT INTO depts(employees) VALUES (20),(10),(30);

CREATE FUNCTION distribute_expenses(amount NUMERIC) RETURNS void AS $$ DECLARE depts_cur CURSOR FOR 
SELECT 
  employees 
FROM 
  depts FOR 
UPDATE 
  ;
total_employees NUMERIC;
expense NUMERIC;
rounding_err NUMERIC := 0.0;
cent NUMERIC;
BEGIN 
SELECT 
  SUM(employees) 
FROM 
  depts INTO total_employees;
FOR dept IN depts_cur LOOP expense := amount * (dept.employees / total_employees);
rounding_err := rounding_err + (
  expense - round(expense, 2)
);
cent := round(rounding_err, 2);
expense := expense + cent;
rounding_err := rounding_err - cent;
UPDATE 
  depts 
SET 
  expenses = round(expense, 2) 
WHERE 
  CURRENT OF depts_cur;
END LOOP;
END $$ VOLATILE LANGUAGE plpgsql;

SELECT distribute_expenses(100.0);
SELECT * FROM depts;