From: Gerardo Castillo <gcastillo@ice.co.cr>
To: pgsql-sql@postgresql.org
Subject: Problem with transaction in functions and tempory tables
Date: Thu, 22 Jul 2004 10:49:32 -0600
Message-ID: <0I19005KZIKF3S@ice.co.cr> (raw)
Hello,
I'm using PostgreSQL 7.4
I have a function wich use temporary tables. I read about temporary tables
and they exists during the session.
But i have to call this function many times in the same sesion with
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of
the funcition.
To avoid this, I used this sintax after de create table statement "ON COMMIT
DROP" which destroy the table in the next commit.
for example, If i run this script many times in the same session there
weren't problems:
begin;
create temporary table test(x integer) ON COMMIT DROP;
INSERT INTO test values(1);
select * from test;
commit;
Then I tried to use this in function:
CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;
INSERT INTO test values (1);
--RETORNA LOS RESULTADOS
FOR res IN SELECT x FROM test LOOP
RETURN NEXT res;
END LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER;
and then I executed the function this way:
BEGIN;
SELECT * FROM f_test() AS R(x INTEGER);
COMMIT;
but in the second execution, it falis with an error wich said that doesn't
exist the relation with OID XXXX... I supose it is because the table doesn't
exist because in the second execution the function couldn't create the table
or it is using an old reference of the dropped table.
I think if I put the begin and the commit inside the function, it will work.
I tried this way, but it doesn't compile:
CREATE OR REPLACE FUNCTION "public"."f_test" () RETURNS SETOF
"pg_catalog"."record" AS'
BEGIN
BEGIN;
CREATE TEMPORARY TABLE test( x integer ) ON COMMIT DROP;
.....
I tried too with START, but without success.
I'd appeciate some help.
Tanks,
Gerardo.
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: gcastillo@ice.co.cr
Subject: Re: Problem with transaction in functions and tempory tables
In-Reply-To: <0I19005KZIKF3S@ice.co.cr>
* 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