Обсуждение: strange plpgsql error
Hi, I have a plpgsql function called irq(IN ulist integer[], .....) It works fine on 8.1 Linux On 8.4 on windows XP running the function gives an error message (Undefined column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange because the underlying query does return that column. I run the create script of irq to create irq2 - and irq2 works fine. I delete irq and rename irq2 to irq and I get the error back. The error is consistent in the sense that if I delete the database and restore it it appears again the same way. Is this a bug? Any suggestion what I should be doing with this? Thanks for the help. SWK -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: > Hi, > I have a plpgsql function called irq(IN ulist integer[], .....) > It works fine on 8.1 Linux > > On 8.4 on windows XP running the function gives an error message (Undefined > column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange > because the underlying query does return that column. > I run the create script of irq to create irq2 - and irq2 works fine. > I delete irq and rename irq2 to irq and I get the error back. > > The error is consistent in the sense that if I delete the database and > restore it it appears again the same way. > > Is this a bug? > Any suggestion what I should be doing with this? > > Thanks for the help. > SWK > -- > View this message in context: > http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent > from the PostgreSQL - general mailing list archive at Nabble.com. Are you using the string 'irq' inside the function? -- Adrian Klaver aklaver@comcast.net
Yes, I use it as a table alias inside the function. Select .......... irq.instreq_min_metcount, irq.ref_deptype, irq.instreq_aggrfunc From .................. instrument_requirement irq ON ........ Adrian Klaver wrote: > > On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: >> Hi, >> I have a plpgsql function called irq(IN ulist integer[], .....) >> It works fine on 8.1 Linux >> >> On 8.4 on windows XP running the function gives an error message >> (Undefined >> column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange >> because the underlying query does return that column. >> I run the create script of irq to create irq2 - and irq2 works fine. >> I delete irq and rename irq2 to irq and I get the error back. >> >> The error is consistent in the sense that if I delete the database and >> restore it it appears again the same way. >> >> Is this a bug? >> Any suggestion what I should be doing with this? >> >> Thanks for the help. >> SWK >> -- >> View this message in context: >> http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent >> from the PostgreSQL - general mailing list archive at Nabble.com. > > Are you using the string 'irq' inside the function? > > -- > Adrian Klaver > aklaver@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847881.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Right, I replace the table alias irq to instreq and the function works. I don't know what could have been the problem: is irq a reserved word, or it's because the table alias has the same name then the function name. Thanks for pointing me to the right direction. SWK SunWuKung wrote: > > > Yes, I use it as a table alias inside the function. > > Select .......... > irq.instreq_min_metcount, > irq.ref_deptype, > irq.instreq_aggrfunc > From .................. instrument_requirement irq ON ........ > > > > > Adrian Klaver wrote: >> >> On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: >>> Hi, >>> I have a plpgsql function called irq(IN ulist integer[], .....) >>> It works fine on 8.1 Linux >>> >>> On 8.4 on windows XP running the function gives an error message >>> (Undefined >>> column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange >>> because the underlying query does return that column. >>> I run the create script of irq to create irq2 - and irq2 works fine. >>> I delete irq and rename irq2 to irq and I get the error back. >>> >>> The error is consistent in the sense that if I delete the database and >>> restore it it appears again the same way. >>> >>> Is this a bug? >>> Any suggestion what I should be doing with this? >>> >>> Thanks for the help. >>> SWK >>> -- >>> View this message in context: >>> http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html >>> Sent >>> from the PostgreSQL - general mailing list archive at Nabble.com. >> >> Are you using the string 'irq' inside the function? >> >> -- >> Adrian Klaver >> aklaver@comcast.net >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847955.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sunday 11 October 2009 2:48:02 pm SunWuKung wrote: > Right, > I replace the table alias irq to instreq and the function works. > I don't know what could have been the problem: is irq a reserved word, or > it's because the table alias has the same name then the function name. > > Thanks for pointing me to the right direction. > SWK > It is not a reserved word (http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html). My guess is it was the name conflict between the function name and the table alias. -- Adrian Klaver aklaver@comcast.net
Yep, it is probably a bug though. Adrian Klaver wrote: > > On Sunday 11 October 2009 2:48:02 pm SunWuKung wrote: >> Right, >> I replace the table alias irq to instreq and the function works. >> I don't know what could have been the problem: is irq a reserved word, or >> it's because the table alias has the same name then the function name. >> >> Thanks for pointing me to the right direction. >> SWK >> > > It is not a reserved word > (http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html). > My guess is it was the name conflict between the function name and the > table > alias. > > > -- > Adrian Klaver > aklaver@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25848214.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Sun, Oct 11, 2009 at 6:21 PM, SunWuKung <Ralikwen@hotmail.com> wrote: > > Yep, > it is probably a bug though. It's not...it's just the way plpgsql works. I prefix all my local variables in plpgsql with an underscore to prevent these kinds of conflicts. merlin
Dunno, if it was a documented behavior it should fail both on on 8.1 Linux and on 8.4 Windows, but it only fails on 8.4 Windows. I will try to create a clear test. Merlin Moncure-2 wrote: > > On Sun, Oct 11, 2009 at 6:21 PM, SunWuKung <Ralikwen@hotmail.com> wrote: >> >> Yep, >> it is probably a bug though. > > It's not...it's just the way plpgsql works. I prefix all my local > variables in plpgsql with an underscore to prevent these kinds of > conflicts. > > merlin > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25857385.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.