Problem with function

[coolminded]

hi all,
i have written one function, but it is showing some error, the code is as follows:

CREATE OR REPLACE FUNCTION fn_name(int8, int8, int8, "varchar", date, date)
RETURNS "varchar" AS
'
declare
dt numeric;
a varchar(20);
start_dt alias for $5;
end_dt alias for $6;
et alias for $4;
begin
if et = \'D\' then
while not start_dt = end_dt loop 
insert into tbl1(event_cd, party_cd, project_cd, event_type,event_dt) values ($1,$2,$3,$4,start_dt)
start_dt:=to_date(start_dt,\'yyyy-mm-dd\') + interval \'1 day\';
end loop;
end if;
return \'a\';
end'
  LANGUAGE 'plpgsql' VOLATILE;
 
 

i want to insert the whole data with only the date incremented by 1 day till it equals to the end date. but it shows the error:
parser:parse error at or near "$6" at character 112

can anyone help me?

TIA

[michaelb]

It often helps to provide information such as what version of database you are using, do you get a compile time or run time error, and, if this is the run time error what are the arguments you pass to the function.

I suppose that you are getting a compile time error, can you post the version of Postgresql?

[michaelb]

I took a closer look at your code and I think you probably have a run time error;
I can spot at least two problems in your function:

1) On line 13 the INSERT statement is not terminated with semi-colon, this is likely the cause of the error message you got.

2) On line 14 you are changing the value of $5.
The function arguments are considered to be constants, and so their aliases.
If you get another error after terminating INSERT with ";" you may have to change your code like this:

   declare
        ... ... ...
        start_dt date ;
 
   begin
        start_dt := $5;
         ... ... ... ...
 

This should allow incrementing value of start_dt

[coolminded]

I took a closer look at your code and I think you probably have a run time error;
I can spot at least two problems in your function:

1) On line 13 the INSERT statement is not terminated with semi-colon, this is likely the cause of the error message you got.

2) On line 14 you are changing the value of $5.
The function arguments are considered to be constants, and so their aliases.
If you get another error after terminating INSERT with ";" you may have to change your code like this:

   declare
        ... ... ...
        start_dt date ;
 
   begin
        start_dt := $5;
         ... ... ... ...
 

This should allow incrementing value of start_dt

thanx for your suggestion michaelb,
it really helped me a lot.
thanx once again

[michaelb]

coolminded, you are very welcome!