Dear all
I have a function like below (simplified). Everything works the way I want it to except for one thing.
After the select statement sum_revenues is NULL instead of the real sum.
Has this something to do with the earlier deletes and inserts not being committed yet?
I assumed they would have been committed at the END marked with (1), but apparently they don't.
How can I fix this?
Thanks to anyone who can enlighten me!
CREATE FUNCTION test(companies IN text[]) returns void AS
$body$
DECLARE
company text;
sum_revenues revenues.revenue%TYPE;
BEGIN
perform general_log('test', 'start');
for idx in array_lower(companies, 1) .. array_upper(companies, 1)
loop
BEGIN
BEGIN
company := lower(companies[idx]);
delete from revenues where ...;
insert into revenues select ..., ..., ... from billing where ...condition1...;
insert into revenues select ..., ..., ... from billing where ...condition2...;
insert into revenues select ..., ..., ... from billing where ...condition3...;
insert into revenues select ..., ..., ... from billing where ...condition4...;
END; --(1)
select sum(revenue) into sum_revenues from revenues;
EXCEPTION
WHEN others
perform general_errlog('test', SQLSTATE, SQLERRM);
END;
end loop;
perform general_log('test', 'end');
EXCEPTION
WHEN others
perform general_errlog('test', SQLSTATE, SQLERRM);
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;