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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026