From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Valaki Valahol <ozoltan9(at)hotmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Stored procedure error |
Date: | 2009-11-19 18:06:43 |
Message-ID: | 162867790911191006j61a1b824w8048d1aa8ccbc2ec@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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 <ozoltan9(at)hotmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2009-11-19 18:19:38 | Re: Stored procedure error |
Previous Message | Orange | 2009-11-19 18:03:47 | data shown from down to up |