Обсуждение: Stored procedure error

Dear Sirs,

My name is Zolt=E1n =D6tv=F6s and I'm from Hungary.=20
Im using PostgreSQL database server from many years and I'm very satisfied =
with this
database.=20
I'm wanted to write a little financial routine in Linux under Lazarus but f=
or the first I've
tested it under pgAdmin. Because I didn't wanted to overload the network tr=
affic with=20
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 eve=
ry time, because
its structure, the number of the columns and rows may vary every time. Here=
 are the=20
two stored procedures I wrote:=20

/****************************************************************/
/*                                          create the emtpy table         =
                               */
/****************************************************************/


drop  function CreateMoneyTable();

create function CreateMoneyTable()
   returns integer as
$$
declare=20
  cmt integer;
  NewId integer;

begin
 cmt:=3D0;

 begin
  drop table MoneyTable;

  cmt:=3D1;
 exception
  when Others then
   cmt:=3D-1;
 end;

 begin
  select Max(mtid) into NewId
  from MoneyTable;

  cmt:=3D2;
 exception
  when Others then
   cmt:=3D-2;
 end;

 if cmt=3D-2 then
   begin
    create global temporary table MoneyTable (
     mtid serial,
     mtdate char(10),
     primary key (mtid)
     )
    on commit preserve rows;

    cmt:=3D3;=20
   exception
    when Others then
     cmt:=3D-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=20
  cmt integer;
  i integer;
  dt0 date;=20
  dt1 date;
  NewId integer;

begin
 cmt:=3D0;
=20
 begin
  select Max(MTID) into NewId
  from MoneyTable;

  cmt:=3D1;
 exception
  when Others then
   cmt:=3D-1;
 end;=20

 if cmt =3D 1 then
   dt0:=3Dto_date(d1,'YYYY-MM-DD');
   dt1:=3Dto_date(d2,'YYYY-MM-DD');

   i:=3D0;
   while ((dt0+i)<=3Ddt1) loop
     insert into MoneyTable (MTDATE) values=20
         (to_char(dt0+i,'YYYY-MM-DD'));=20=20

    i:=3Di+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;
=20

/****************************************************************/




Everything works fine for the first time. But if I call it for the second t=
ime in the same=20
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 se=
cond time the create table
command it's unable to complete when it gets to the insert command so the i=
nsert command doesn't=20
finds the target table. Like the database server internal execution it shou=
ld be too fast, I guess...
Naturally I've tried all these scripts as the postgres superuser.=20
After filling the dates in this temporary table I would like to add the col=
umns which contains the=20
financial datas.=20
I would prefer to use temporary tables, because this program it's used in a=
 network enviroment.=20
So my first question is that what do I make wrong that I can't run these sc=
ripts 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=E1n =D6tv=F6s
Hungary



=20=09=09=20=09=20=20=20=09=09=20=20
_________________________________________________________________
Windows Live: Make it easier for your friends to see what you=92re up to on=
 Facebook.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/so=
cial-network-basics.aspx?ocid=3DPID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_2:092=
009=

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.

Valaki Valahol wrote:
> ERROR: relation with OID 28461 does not exist


http://wiki.postgresql.org/wiki/FAQ#Why_do_I_get_.22relation_with_OID_.23.23.23.23.23_does_not_exist.22_errors_when_accessing_temporary_tables_in_PL.2FPgSQL_functions.3F

Upgrading to 8.4 (or 8.3) should fix that.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com