Сделал так (нужно наверное как то оптимизировать ее), все работает, но есть одно НО: если для какого то номера нет значения (например, rec2.summary), то результат не заносится в таблицу t_numbers.
CREATE OR REPLACE FUNCTION updatebalance()
RETURNS void AS
$BODY$
DECLARE
rec1 RECORD;
rec2 RECORD;
rec3 RECORD;
rec4 RECORD;
rec5 RECORD;
rec6 RECORD;
BEGIN
FOR rec1 IN (SELECT n.vnumber, t.vname, noutfavoritecost, noutsystemcost, noutothercost, noutcityphonecost, noutinternationalcost, nsmscost, nmmscost, nwapcost FROM t_numbers n -- t_numbers + t_tariff
FULL JOIN t_contract c
FULL JOIN t_tariff t ON c.ntariffid=t.id
ON c.id=n.ncontractid
GROUP BY n.vnumber, t.noutfavoritecost, t.vname, noutsystemcost, noutothercost, noutcityphonecost, noutinternationalcost, nsmscost, nmmscost, nwapcost)
LOOP
FOR rec2 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue) as summary FROM t_numbers n -- t_numbers + t_units
LEFT JOIN t_units c
LEFT JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'WAP'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec3 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.nvalue) as summary FROM t_numbers n -- t_numbers + t_cashactions
FULL JOIN t_cashactions c
FULL JOIN t_cashactiontype t ON c.ncashactiontypeid=t.id
ON c.nnumberid=n.id
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec4 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue) as summary FROM t_numbers n -- t_numbers + t_units
LEFT JOIN t_units c
LEFT JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'SMS'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec5 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue) as summary FROM t_numbers n -- t_numbers + t_units
LEFT JOIN t_units c
LEFT JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'MMS'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec6 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue) as summary FROM t_numbers n -- t_numbers + t_units
LEFT JOIN t_units c
LEFT JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'вызов'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
UPDATE t_numbers SET nbalance = (rec1.nwapcost*rec2.summary + rec1.noutsystemcost*rec6.summary + rec1.nsmscost*rec4.summary + rec1.nmmscost*rec5.summary)+rec3.summary WHERE id=rec2.nnumberid AND id=rec3.nnumberid;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
Как оптимизировать можно эту функцию и как исправить ошибку с отсутствием значения?