From: | "Burak Seydioglu" <buraks78(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Is setQuerySnapshot called for embedded plpgsql function calls? |
Date: | 2007-02-02 02:45:38 |
Message-ID: | 1b8a973c0702011845j55162fd0xbbf344ec6d35370c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I have two plpgsql functions. first_function() calls the second one
with a repetitive EXECUTE second_func() statement. Every time the
second_func() is called, it creates a record in the table and this new
data is then used in the consecutive second_func() call.
For some reason, the consecutive second_func() calls do not see the
newly inserted data. So the total for the next second_func() call
always remains zero. Please see the code below.
Is this because setQuerySnapshot() is not called for embedded plpgsql
functions but only for the first_function() call?
I am runnging 7.4 btw.
Any input is appreciated. Thank you very much.
Burak
--
-- FIRST FUNCTION
--
CREATE OR REPLACE FUNCTION sales() RETURNS void AS '
DECLARE
var_result RECORD;
BEGIN
FOR var_result IN EXECUTE ''SELECT seller_id FROM seller'' LOOP
EXECUTE individual_sales(var_result.seller_id);
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
--
-- SECOND FUNCTION
--
CREATE OR REPLACE FUNCTION individual_sales(bigint,bigint) RETURNS void AS '
DECLARE
var_id ALIAS FOR $1;
var_sponsor ALIAS FOR $2;
var_query TEXT;
var_result RECORD;
var_total INTEGER;
BEGIN
var_query := ''SELECT SUM(sales) AS s FROM sales WHERE sponsor='' ||
quote_literal(var_id);
FOR var_result IN EXECUTE var_query LOOP
IF var_result.s IS NOT NULL OR var_result.s != 0 THEN
var_total := var_total + var_result.s;
END IF;
END LOOP;
--
-- insert sales stats for associate
--
EXECUTE
''INSERT INTO sales VALUES(''
|| quote_literal(var_id) || '',''
|| quote_literal(var_sponsor) || '',''
|| quote_literal(var_total) ||
'')'';
RETURN;
END;
' LANGUAGE 'plpgsql';
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-02-02 03:40:07 | Re: Is setQuerySnapshot called for embedded plpgsql function calls? |
Previous Message | Bruno Wolff III | 2007-02-01 19:11:33 | Re: Send Email from Postgressql |