Re: PL/PgSQL Create/Drop Table Issue - Mailing list pgsql-bugs
From | Bruce Momjian |
---|---|
Subject | Re: PL/PgSQL Create/Drop Table Issue |
Date | |
Msg-id | 200305151612.h4FGCgW05205@candle.pha.pa.us Whole thread Raw |
In response to | PL/PgSQL Create/Drop Table Issue (Database Administrator <dba@vilaj.com>) |
List | pgsql-bugs |
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
pgsql-bugs by date: