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