Re: Temporary table weirdness

Yes, this is a known problem.  You need to use plpgsql EXECUTE command
for any function query that deals with temp tables.

---------------------------------------------------------------------------

Joseph Barillari wrote:

Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> Hi,
> 
> I have a function that creates and destroys temporary tables in the
> course of its work, and have run into trouble if the function
> is executed more than once during a database session. A small
> proof-of-concept is attached below:
> 
> cal=> create or replace function frob() returns integer as 'begin create temporary table foo(bar INT); insert into
foo(bar) values (1); drop table foo; return 1; end;' language 'plpgsql';
 
> CREATE
> 
> (seems pretty standard)
> 
> cal=> select frob();
>  frob 
> ------
>     1
> (1 row)
> 
> (OK so far...)
> 
> cal=> select frob();
> NOTICE:  Error occurred while executing PL/pgSQL function frob
> NOTICE:  line 1 at SQL statement
> ERROR:  Relation 68141 does not exist
> 
> On the second call, PostgreSQL chokes. Any suggestions as to why
> would be appreciated.
> 
> Oddly enough, redefining the function eliminates the error for the
> next function call.
> 
> Thanks,
> 
> Joe
-- End of PGP section, PGP failed!

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026