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(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073