Skip site navigation (1) Skip section navigation (2)

Stored procedure error

From: Valaki Valahol <ozoltan9(at)hotmail(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Stored procedure error
Date: 2009-11-19 14:16:08
Message-ID: SNT126-W44D1E9499DA5F703883E29BA20@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-bugs
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.
http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_2:092009

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-11-19 15:13:39
Subject: Re: BUG #5198: Plain dump: wrong field order for inherited tables
Previous:From: Hiroshi InoueDate: 2009-11-19 11:40:50
Subject: Re: BUG #5194: fire IDispatch Error #3015 when run PutCollect() in ADO

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group