From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql@postgresql.org
Subject: select sum within transaction problem
Date: Tue, 11 Sep 2007 17:45:17 +0200
Message-ID: <46E6D42C.A3DD.0030.0@indicator.be> (raw)
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Bart.Degryse@indicator.be
Subject: Re: select sum within transaction problem
In-Reply-To: <46E6D42C.A3DD.0030.0@indicator.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox