By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,751 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Problem with function

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

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION fn_name(int8, int8, int8, "varchar", date, date)
  2. RETURNS "varchar" AS
  3. '
  4. declare
  5. dt numeric;
  6. a varchar(20);
  7. start_dt alias for $5;
  8. end_dt alias for $6;
  9. et alias for $4;
  10. begin
  11. if et = \'D\' then
  12. while not start_dt = end_dt loop 
  13. insert into tbl1(event_cd, party_cd, project_cd, event_type,event_dt) values ($1,$2,$3,$4,start_dt)
  14. start_dt:=to_date(start_dt,\'yyyy-mm-dd\') + interval \'1 day\';
  15. end loop;
  16. end if;
  17. return \'a\';
  18. end'
  19.   LANGUAGE 'plpgsql' VOLATILE;
  20.  
  21.  
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
Jul 15 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 534
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?
Jul 15 '07 #2

Expert 100+
P: 534
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:

Expand|Select|Wrap|Line Numbers
  1.    declare
  2.         ... ... ...
  3.         start_dt date ;
  4.  
  5.    begin
  6.         start_dt := $5;
  7.          ... ... ... ...
  8.  
This should allow incrementing value of start_dt
Jul 15 '07 #3

100+
P: 138
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:

Expand|Select|Wrap|Line Numbers
  1.    declare
  2.         ... ... ...
  3.         start_dt date ;
  4.  
  5.    begin
  6.         start_dt := $5;
  7.          ... ... ... ...
  8.  
This should allow incrementing value of start_dt

thanx for your suggestion michaelb,
it really helped me a lot.
thanx once again
Jul 16 '07 #4

Expert 100+
P: 534
coolminded, you are very welcome!
Jul 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.