creating/dropping tables inside functions?

From: "George Pavlov" <gpavlov(at)mynewplace(dot)com>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: creating/dropping tables inside functions?
Date: 2007-09-11 15:50:02
Message-ID: 8C5B026B51B6854CBE88121DBF097A8601215F9B@ehost010-33.exch010.intermedia.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2007-09-11 15:50:38 avg() of array values
Previous Message Greg Smith 2007-09-11 15:24:19 Re: Hardware recommendation: which is best