pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect sum within transaction problem
2+ messages / 2 participants
[nested] [flat]
* select sum within transaction problem
@ 2007-09-11 15:45 Bart Degryse <Bart.Degryse@indicator.be>
2007-09-12 01:09 ` Re: select sum within transaction problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Bart Degryse @ 2007-09-11 15:45 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: select sum within transaction problem
2007-09-11 15:45 select sum within transaction problem Bart Degryse <Bart.Degryse@indicator.be>
@ 2007-09-12 01:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-09-12 01:09 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql
"Bart Degryse" <Bart.Degryse@indicator.be> writes:
> 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?
No. I suspect the problem is hidden somewhere in the code you didn't
show us. In particular you should check whether the insert/selects
are actually finding anything to insert ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-09-12 01:09 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-11 15:45 select sum within transaction problem Bart Degryse <Bart.Degryse@indicator.be>
2007-09-12 01:09 ` Tom Lane <tgl@sss.pgh.pa.us>
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