Stored procedure error - Mailing list pgsql-bugs

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=