strange plpgsql error

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.