From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200204291404.g3TE46A26180@candle.pha.pa.us>
Subject: Re: Temporary table weirdness
In-Reply-To: <m3elgzni0f.fsf@washer.barillari.org>
To: Joseph Barillari <jbarilla@princeton.edu>
Date: Mon, 29 Apr 2002 10:04:06 -0400 (EDT)
Cc: pgsql-sql <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


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, Pennsylvania 19026