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
