Thread: strange plpgsql error

strange plpgsql error

From
SunWuKung
Date:
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.


Re: strange plpgsql error

From
Adrian Klaver
Date:
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

Re: strange plpgsql error

From
SunWuKung
Date:

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.


Re: strange plpgsql error

From
SunWuKung
Date:
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.


Re: strange plpgsql error

From
Adrian Klaver
Date:
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

Re: strange plpgsql error

From
SunWuKung
Date:
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.


Re: strange plpgsql error

From
Merlin Moncure
Date:
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

Re: strange plpgsql error

From
Ralikwen
Date:
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.