select sum within transaction problem - Mailing list pgsql-sql

From Bart Degryse
Subject select sum within transaction problem
Date
Msg-id 46E6D42C.A3DD.0030.0@indicator.be
Whole thread Raw
Responses Re: select sum within transaction problem
List 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;

pgsql-sql by date:

Previous
From: Dave Page
Date:
Subject: Re: Does postgresql8.2 supports multithreading?
Next
From: "Josh Tolley"
Date:
Subject: Re: Does postgresql8.2 supports multithreading?