Subject: select sum within transaction problem Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part6D4AC01D.0__=" X-imss-version: 2.048 X-imss-result: Passed X-imss-approveListMatch: *@indicator.be X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.138 tagged_above=0 required=5 tests=AWL=0.221, BAYES_40=-0.185, HTML_MESSAGE=0.001, RDNS_NONE=0.1, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 200709/81 X-Sequence-Number: 29294 --=__Part6D4AC01D.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable 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! =20 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 :=3D 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; --=__Part6D4AC01D.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML

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

company :=3D lower(companie= s[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('tes= t', SQLSTATE, SQLERRM);

END;

end loop;

perform general_log('test', 'end');

EXCEPTION

WHEN others

perform general_errlog('test', SQLSTATE, SQLERRM);<= /DIV>

END;

$body$

LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

--=__Part6D4AC01D.0__=-- From tgl@sss.pgh.pa.us Wed Sep 12 01:09:21 2007 Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 15A079F9798 for ; Tue, 11 Sep 2007 22:09:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 87051-10 for ; Tue, 11 Sep 2007 22:09:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 44B4E9F9618 for ; Tue, 11 Sep 2007 22:09:16 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l8C19D2f028188; Tue, 11 Sep 2007 21:09:13 -0400 (EDT) To: "Bart Degryse" cc: pgsql-sql@postgresql.org Subject: Re: select sum within transaction problem In-reply-to: <46E6D42C.A3DD.0030.0@indicator.be> References: <46E6D42C.A3DD.0030.0@indicator.be> Comments: In-reply-to "Bart Degryse" message dated "Tue, 11 Sep 2007 17:45:17 +0200" Date: Tue, 11 Sep 2007 21:09:13 -0400 Message-ID: <28187.1189559353@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/89 X-Sequence-Number: 29302 "Bart Degryse" 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