Thread: BUG #3590: Error while in Plpgsql :

BUG #3590: Error while in Plpgsql :

From
"Abdus Samad Ansari"
Date:
The following bug has been logged online:

Bug reference:      3590
Logged by:          Abdus Samad Ansari
Email address:      abdus.samad.ansari@gmail.com
PostgreSQL version: 7.4.2.
Operating system:   Fedora 2 Linux
Description:        Error while in Plpgsql :
Details:

I am using a plpgsql function and in this I am issuing a insert as :
Insert into pis.hrt_emp_leave_bin
(emp_no,leave_cd,curr_dt,bin_sl_no,cal_yr,tran_typ,open_bal,ytd_availed,avai
led) values (EmployeeNumber, LeaveCode, CurrentDate,
Bin_Serial_No,to_number(to_char(CurrentDate,''YYYY''),''9999''),''CM'',
OpenBal,YtdAvailed+LeaveVal,LeaveVal);

This insert statement while run give an error message :
ERROR:  syntax error at or near "$1" at character 35

The problem is with the column named "availed", while I renamed the column
availed as "availd", the same statement works fine.

I could not understand this, whether the word availed is a reserve word for
plpgsql? or something else?


Thanks in advance.

Abdus Samad Ansari

Re: BUG #3590: Error while in Plpgsql :

From
Tom Lane
Date:
"Abdus Samad Ansari" <abdus.samad.ansari@gmail.com> writes:
> I am using a plpgsql function and in this I am issuing a insert as :
> Insert into pis.hrt_emp_leave_bin
> (emp_no,leave_cd,curr_dt,bin_sl_no,cal_yr,tran_typ,open_bal,ytd_availed,avai
> led) values (EmployeeNumber, LeaveCode, CurrentDate,
> Bin_Serial_No,to_number(to_char(CurrentDate,''YYYY''),''9999''),''CM'',
> OpenBal,YtdAvailed+LeaveVal,LeaveVal);

> This insert statement while run give an error message :
> ERROR:  syntax error at or near "$1" at character 35

This probably means that one of the field names in the statement (if I
counted characters right, probably "emp_no") is the same as the name of
one of the plpgsql variables in the function.  You need to change the
variables to not conflict with any of the table or field names you need
to reference in the queries of that function, because plpgsql is not
very bright about telling when it should substitute and when it should
not.

            regards, tom lane