Re: PL/PgSQL Create/Drop Table Issue

See FAQ item about this --- use EXECUTE plpgsql command.

---------------------------------------------------------------------------

Database Administrator wrote:
> I believe I've found a problem in creating and dropping tables within
> PL/PgSQL functions. If you create a table, do some work with it, then
> drop it from within a PL/PgSQL function, it will work correctly the
> first time through. On subsequent executions of the function, however,
> attempting to insert data into the table will result in an error. The
> error report follows as does a script which demonstrates the issue clearly.
>
> ====  START OF ERROR OUTPUT  ====
>
> psql:./test_temp_table.sql:33: WARNING:  Error occurred while executing
> PL/pgSQL function test_temp_table
> psql:./test_temp_table.sql:33: WARNING:  line 5 at SQL statement
> psql:./test_temp_table.sql:33: ERROR:  pg_class_aclcheck: relation
> 3326289 not found
>
> ====   END OF ERROR OUTPUT   ====
>
>
> ====     START OF SCRIPT     ====
>
> CREATE TABLE my_permanent_table (
>      key_value serial NOT NULL PRIMARY KEY,
>      statement text NOT NULL);
>
> CREATE OR REPLACE FUNCTION test_temp_table()
> RETURNS boolean AS '
>      BEGIN
>     -- NOTE: regular and temporary tables both affected the same
>
>          CREATE TEMPORARY TABLE my_temp_table (
>              statement text NOT NULL);
>
>          INSERT INTO my_temp_table (statement)
>              VALUES (''We can''''t play this game anymore...'');
>
>          INSERT INTO my_temp_table (statement)
>              VALUES (''...but can we still be friends?'');
>
>          INSERT INTO my_permanent_table (statement)
>              SELECT statement
>              FROM my_temp_table;
>
>          DROP TABLE my_temp_table;
>
>          RETURN true;
>      END;
> ' LANGUAGE 'plpgsql';
>
> SELECT test_temp_table();
>
> SELECT key_value,
>      statement
> FROM my_permanent_table;
>
> SELECT test_temp_table();
>
> ====      END OF SCRIPT      ====
>
>
> ====  VERSION/PLATFORM INFO  ====
>
> PostgreSQL 7.3.2 on powerpc-apple-darwin6.3, compiled by GCC gcc (GCC)
> 3.1 20020420 (prerelease)
>
> Also tested on Debian Linux 3.0.x on Intel x86 with same result.
>
> =================================
>
>
> Thanks for looking into this.
>
> --
> Database Administrator, vilaj.com, LLC
> <http://www.vilaj.com/>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@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