Hello You cannot to drop temporary table in stored procedure. Then you can have a problem. There are two possibilities: a) you will upgrade to 8.3, b) you will change code - minimum is replace drop table by truncate table
please, read http://www.postgres.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL Regards Pavel Stehule p.s. this isn't bug - please use pg_general mailing_list 2009/11/19 Valaki Valahol <ozolt...@hotmail.com>: > Dear Sirs, > > My name is Zoltán Ötvös and I'm from Hungary. > Im using PostgreSQL database server from many years and I'm very satisfied > with this > database. > I'm wanted to write a little financial routine in Linux under Lazarus but > for the first I've > tested it under pgAdmin. Because I didn't wanted to overload the network > traffic with > data manipulation I've thought that make all the data manipulation routine > on the server > side in a stored procedure. The main goal is to create the MoneyTable table > dynamically > every time and fill it with the actual data. I have to create the table > every time, because > its structure, the number of the columns and rows may vary every time. Here > are the > two stored procedures I wrote: > > /****************************************************************/ > /* create the emtpy table > */ > /****************************************************************/ > > drop function CreateMoneyTable(); > > create function CreateMoneyTable() > returns integer as > $$ > declare > cmt integer; > NewId integer; > > begin > cmt:=0; > > begin > drop table MoneyTable; > > cmt:=1; > exception > when Others then > cmt:=-1; > end; > > begin > select Max(mtid) into NewId > from MoneyTable; > > cmt:=2; > exception > when Others then > cmt:=-2; > end; > > if cmt=-2 then > begin > create global temporary table MoneyTable ( > mtid serial, > mtdate char(10), > primary key (mtid) > ) > on commit preserve rows; > > cmt:=3; > exception > when Others then > cmt:=-3; > end; > end if; > > return cmt; > end; > $$ > LANGUAGE 'plpgsql' VOLATILE; > > > /****************************************************************/ > /* fill the table with start data > */ > /****************************************************************/ > > drop function FillMoneyTable(d1 char(10), d2 char(10)); > > create function FillMoneyTable(d1 char(10), d2 char(10)) > returns integer as > $$ > declare > cmt integer; > i integer; > dt0 date; > dt1 date; > NewId integer; > > begin > cmt:=0; > > begin > select Max(MTID) into NewId > from MoneyTable; > > cmt:=1; > exception > when Others then > cmt:=-1; > end; > > if cmt = 1 then > dt0:=to_date(d1,'YYYY-MM-DD'); > dt1:=to_date(d2,'YYYY-MM-DD'); > > i:=0; > while ((dt0+i)<=dt1) loop > insert into MoneyTable (MTDATE) values > (to_char(dt0+i,'YYYY-MM-DD')); > > i:=i+1; > end loop; > end if; > > return cmt; > end; > $$ > LANGUAGE 'plpgsql' VOLATILE; > > /****************************************************************/ > /* check if it works > */ > /****************************************************************/ > > select CreateMoneyTable(); > > select FillMoneyTable('2009-09-01','2009-09-13'); > > select * from MoneyTable; > > /****************************************************************/ > > Everything works fine for the first time. But if I call it for the second > time in the same > query window under pgAdmin then it gives the following error messages: > > > ERROR: relation with OID 28461 does not exist > CONTEXT: SQL statement "INSERT INTO MoneyTable (MTDATE) values (to_char( $1 > + $2 ,'YYYY-MM-DD'))" > PL/pgSQL function "fillmoneytable" line 18 at SQL statement > > > ********** Error ********** > > ERROR: relation with OID 28461 does not exist > SQL state: 42P01 > Context: SQL statement "INSERT INTO MoneyTable (MTDATE) values (to_char( $1 > + $2 ,'YYYY-MM-DD'))" > PL/pgSQL function "fillmoneytable" line 18 at SQL statement > > > I can't understand that why it returns with error. It seems that for the > second time the create table > command it's unable to complete when it gets to the insert command so the > insert command doesn't > finds the target table. Like the database server internal execution it > should be too fast, I guess... > Naturally I've tried all these scripts as the postgres superuser. > After filling the dates in this temporary table I would like to add the > columns which contains the > financial datas. > I would prefer to use temporary tables, because this program it's used in a > network enviroment. > So my first question is that what do I make wrong that I can't run these > scripts for many times? > Second question is that how it should work ? What's the solution ? > > Please help!.... > > Thank You for Your help in advance! > > Best regards > Zoltán Ötvös > Hungary > > > > > ________________________________ > Windows Live: Make it easier for your friends to see what you’re up to on > Facebook. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
