creating/dropping tables inside functions? - Mailing list pgsql-general

From George Pavlov
Subject creating/dropping tables inside functions?
Date
Msg-id 8C5B026B51B6854CBE88121DBF097A8601215F9B@ehost010-33.exch010.intermedia.net
Whole thread Raw
Responses Re: creating/dropping tables inside functions?
List pgsql-general
i am trying to create a temp table inside a plpgsql function (i just
need a temporary place to hold data, but it is too complex for any other
data structure). unfortunately if i call the function again within the
same session the temp table still exists and the function fails. if i
drop the temp table explicitly the next time around the function
complains of missing function (by OID, not by name). what's the right
way to do this? details (with a pared down setup) follow:

start with a function that does not clean up its temp table:

foo=> create or replace function f() returns int as
foo-> $body$
foo$> declare x int;
foo$> begin
foo$>   create temp table t as select 1::int as a;
foo$>   select a from t into x;
foo$>   return x;
foo$> end;
foo$> $body$
foo-> language 'plpgsql'
foo-> ;
CREATE FUNCTION
foo=> select * from f();
 f
---
 1
(1 row)

foo=> select * from f();
ERROR:  relation "t" already exists
CONTEXT:  SQL statement "create temp table t as select 1::int as a"
PL/pgSQL function "f" line 3 at SQL statement

so the first invocation is ok, but the second one still sees the temp
table in scope.

now try a function that does clean up:

foo=> drop table t;
DROP TABLE
foo=> create or replace function f() returns int as
foo-> $body$
foo$> declare x int;
foo$> begin
foo$>   create temp table t as select 1::int as a;
foo$>   select a from t into x;
foo$>   drop table t;
foo$>   return x;
foo$> end;
foo$> $body$
foo-> language 'plpgsql'
foo-> ;
CREATE FUNCTION
foo=> select * from f();
 f
---
 1
(1 row)

foo=> select * from f();
ERROR:  relation with OID 1469396 does not exist
CONTEXT:  SQL statement "SELECT  a from t"
PL/pgSQL function "f" line 4 at select into variables

the second invocation does not see the newly created temp table...

pgsql-general by date:

Previous
From: Alban Hertroys
Date:
Subject: avg() of array values
Next
From: "George Pavlov"
Date:
Subject: Re: Question about a query with two count fields