Re: Stored procedure error

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/pgS=
QL

Regards
Pavel Stehule

p.s. this isn't bug - please use pg_general mailing_list

2009/11/19 Valaki Valahol <ozoltan9@hotmail.com>:
> Dear Sirs,
>
> My name is Zolt=C3=A1n =C3=96tv=C3=B6s 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 tab=
le
> 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. He=
re
> are the
> two stored procedures I wrote:
>
> /****************************************************************/
> /*=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 create the emtpy table
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 */
> /****************************************************************/
>
> drop=C2=A0 function CreateMoneyTable();
>
> create function CreateMoneyTable()
> =C2=A0=C2=A0 returns integer as
> $$
> declare
> =C2=A0 cmt integer;
> =C2=A0 NewId integer;
>
> begin
> =C2=A0cmt:=3D0;
>
> =C2=A0begin
> =C2=A0 drop table MoneyTable;
>
> =C2=A0 cmt:=3D1;
> =C2=A0exception
> =C2=A0 when Others then
> =C2=A0=C2=A0 cmt:=3D-1;
> =C2=A0end;
>
> =C2=A0begin
> =C2=A0 select Max(mtid) into NewId
> =C2=A0 from MoneyTable;
>
> =C2=A0 cmt:=3D2;
> =C2=A0exception
> =C2=A0 when Others then
> =C2=A0=C2=A0 cmt:=3D-2;
> =C2=A0end;
>
> =C2=A0if cmt=3D-2 then
> =C2=A0=C2=A0 begin
> =C2=A0=C2=A0=C2=A0 create global temporary table MoneyTable (
> =C2=A0=C2=A0 =C2=A0 mtid serial,
> =C2=A0 =C2=A0=C2=A0 mtdate char(10),
> =C2=A0=C2=A0 =C2=A0 primary key (mtid)
> =C2=A0 =C2=A0=C2=A0 )
> =C2=A0 =C2=A0 on commit preserve rows;
>
> =C2=A0=C2=A0=C2=A0 cmt:=3D3;
> =C2=A0=C2=A0 exception
> =C2=A0=C2=A0=C2=A0 when Others then
> =C2=A0=C2=A0 =C2=A0 cmt:=3D-3;
> =C2=A0=C2=A0 end;
> =C2=A0end if;
>
> =C2=A0return cmt;
> end;
> $$
> =C2=A0LANGUAGE 'plpgsql' VOLATILE;
>
>
> /****************************************************************/
> /*=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 fi=
ll the table with start data
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 */
> /****************************************************************/
>
> drop=C2=A0 function FillMoneyTable(d1 char(10), d2 char(10));
>
> create function FillMoneyTable(d1 char(10), d2 char(10))
> =C2=A0=C2=A0 returns integer as
> $$
> declare
> =C2=A0 cmt integer;
> =C2=A0 i integer;
> =C2=A0 dt0 date;
> =C2=A0 dt1 date;
> =C2=A0 NewId integer;
>
> begin
> =C2=A0cmt:=3D0;
>
> =C2=A0begin
> =C2=A0 select Max(MTID) into NewId
> =C2=A0 from MoneyTable;
>
> =C2=A0 cmt:=3D1;
> =C2=A0exception
> =C2=A0 when Others then
> =C2=A0=C2=A0 cmt:=3D-1;
> =C2=A0end;
>
> =C2=A0if cmt =3D 1 then
> =C2=A0=C2=A0 dt0:=3Dto_date(d1,'YYYY-MM-DD');
> =C2=A0=C2=A0 dt1:=3Dto_date(d2,'YYYY-MM-DD');
>
> =C2=A0=C2=A0 i:=3D0;
> =C2=A0=C2=A0 while ((dt0+i)<=3Ddt1) loop
> =C2=A0=C2=A0=C2=A0=C2=A0 insert into MoneyTable (MTDATE) values
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (to_char(dt0+i,'YYYY-MM-=
DD'));
>
> =C2=A0=C2=A0=C2=A0 i:=3Di+1;
> =C2=A0=C2=A0 end loop;
> =C2=A0end if;
>
> =C2=A0return cmt;
> end;
> $$
> =C2=A0LANGUAGE 'plpgsql' VOLATILE;
>
> /****************************************************************/
> /*=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 check if it works
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 */
> /****************************************************************/
>
> 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:=C2=A0 relation with OID 28461 does not exist
> CONTEXT:=C2=A0 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=C3=A1n =C3=96tv=C3=B6s
> Hungary
>
>
>
>
> ________________________________
> Windows Live: Make it easier for your friends to see what you=E2=80=99re =
up to on
> Facebook.