Problem with transaction in functions and tempory tables
am 22.07.2004 18:49:32 von gcastilloThis is a multi-part message in MIME format.
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT
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.
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT
tables and they exists during the session.
diferents parameters and expecting different results. So, there is a problem
because the temporary table already exists during the second execution of the
funcition.
COMMIT DROP" which destroy the table in the next
commit.
there weren't problems:
table test(x integer) ON COMMIT
DROP;
AS'
BEGIN
TEMPORARY TABLE test( class=187244115-21072004> x integer
) ON COMMIT DROP;
INTO test class=772173421-21072004> values (1);
--RETORNA LOS RESULTADOS
FOR res IN SELECT x
FROM class=531441916-22072004>test LOOP
RETURN NEXT res;
END
LOOP;
RETURN;
END;
'LANGUAGE 'plpgsql' IMMUTABLE
CALLED ON NULL INPUT SECURITY DEFINER;
way:
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.
will work.
OR REPLACE FUNCTION "public"."f_test" ()
RETURNS SETOF "pg_catalog"."record" AS'
class=187244115-21072004>BEGIN
BEGIN;
TEMPORARY TABLE test( class=187244115-21072004> x integer
) ON COMMIT DROP;
success.
--Boundary_(ID_bvUHanHEVT60/w6NsSufQg)--