We have an FAQ for this:
<H4><A name="4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?</H4>
It says temporary tables, but it is valid for real tables too when you
creating/dropping them in the function.
---------------------------------------------------------------------------
PostgreSQL Bugs List wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1059
> Logged by: Wilhelm
>
> Email address: wilhelm.pakulla@gmx.de
>
> PostgreSQL version: 7.4
>
> Operating system: Linux
>
> Description: Second Call of a PGSQL-function fails
>
> Details:
>
> -- The Source:
>
> -- Init Stuff
> DROP FUNCTION plpgsql_call_handler () CASCADE;
> CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
> '$libdir/plpgsql' LANGUAGE C;
>
> CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
> HANDLER plpgsql_call_handler;
>
> -- The function
>
> CREATE FUNCTION f (INTEGER) RETURNS INTEGER
> AS '
> BEGIN
> CREATE TABLE test ( x INTEGER );
>
> -- Without this insert, everything works well...
> INSERT INTO test VALUES (1);
>
> DROP TABLE test CASCADE;
>
> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- That works.
> SELECT f(1);
>
> -- Second Call fails.
> SELECT f(1);
>
> -- Thanks in advance, Wilhelm
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
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
