Re: BUG #1059: Second Call of a PGSQL-function fails

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