Uh, have you read the FAQ item about plpgsql and temporary tables?
---------------------------------------------------------------------------
Oliver Siegmar wrote:
> Hello,
>
> I've probably found a temp table bug in PostgreSQL (tested with 7.4.7 and
> 8.0.1 on Linux x86).
>
>
> Here's a demonstration of the bug:
>
> CREATE FUNCTION testfunction()
> RETURNS void
> AS '
> BEGIN
> CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP;
>
> INSERT INTO testtable (field) VALUES (1);
>
> -- DROP TABLE testtable;
>
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
>
> database=# SELECT testfunction();
>
> testfunction
> --------------
>
> (1 row)
>
> database=# SELECT testfunction();
> ERROR: relation with OID 29308882 does not exist
> CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)"
> PL/pgSQL function "testfunction" line 4 at SQL statement
>
>
> No transaction has been started manually.
>
> If I drop the temporary testtable manually within the PL/pgSQL function,
> everything runs fine. Bug or feature? ;-)
>
>
> Cheers,
> Oliver
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
