pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Problem with transaction in functions and tempory tables
2+ messages / 2 participants
[nested] [flat]

* Problem with transaction in functions and tempory tables
@ 2004-07-22 16:49  Gerardo Castillo <gcastillo@ice.co.cr>
  0 siblings, 1 reply; 2+ messages in thread

From: Gerardo Castillo @ 2004-07-22 16:49 UTC (permalink / raw)
  To: pgsql-sql

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.
 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Problem with transaction in functions and tempory tables
@ 2004-07-22 17:27  Stephan Szabo <sszabo@megazone.bigpanda.com>
  parent: Gerardo Castillo <gcastillo@ice.co.cr>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2004-07-22 17:27 UTC (permalink / raw)
  To: Gerardo Castillo <gcastillo@ice.co.cr>; +Cc: pgsql-sql

On Thu, 22 Jul 2004, Gerardo Castillo wrote:

> 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.

If you're going to use temporary tables in a plpgsql function, you really
need to use the table only through EXECUTE commands so that the plans
don't get saved.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2004-07-22 17:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-22 16:49 Problem with transaction in functions and tempory tables Gerardo Castillo <gcastillo@ice.co.cr>
2004-07-22 17:27 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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