Thread: creating/dropping tables inside functions?

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

George Pavlov <gpavlov@mynewplace.com> schrieb:
> 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...

Right, normal behavior. You can't use DDL in functions in this way, use
EXECUTE 'your DDL' or wait for 8.3. Read more about this:

http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°