Thread: BUG #5108: plpgsql function name conflict with table alias

The following bug has been logged online:

Bug reference:      5108
Logged by:          Balazs Klein
Email address:      Balazs.Klein@gmail.com
PostgreSQL version: 8.4.1
Operating system:   Windows XP
Description:        plpgsql function name conflict with table alias
Details:

I have a plpgsql function called
irq(IN ulist integer[], .....)

defined as

Select ..........
irq.instreq_min_metcount,
irq.ref_deptype,
irq.instreq_aggrfunc
From .................. instrument_requirement irq ON ........


It works fine on 8.1 Linux

On 8.4.1 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.

I replace the table alias irq to instreq and the function works.

I believe it is a conflict between the table alias and the function name.

I raised the issue and got help on the mail list:
http://www.nabble.com/strange-plpgsql-error-td25847709.html#a25848066

Thanks and regards.
Balazs

"Balazs Klein" <Balazs.Klein@gmail.com> writes:
> On 8.4.1 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.

There's really no way to investigate that without a *complete* example.

            regards, tom lane

Hi,
the test case below runs fine for me if the function name is not instreq and
returns an error if it is.

Regards.
Balazs



CREATE TABLE aaa (
    instreq_id integer
);


INSERT INTO aaa (instreq_id) VALUES (223);
INSERT INTO aaa (instreq_id) VALUES (224);
INSERT INTO aaa (instreq_id) VALUES (225);
INSERT INTO aaa (instreq_id) VALUES (226);
INSERT INTO aaa (instreq_id) VALUES (227);
INSERT INTO aaa (instreq_id) VALUES (228);
INSERT INTO aaa (instreq_id) VALUES (229);


CREATE OR REPLACE FUNCTION instreq(OUT instreq_id integer)
  RETURNS SETOF integer AS
$BODY$
DECLARE
  rec  record;

BEGIN

FOR rec IN
Select instreq.instreq_id From aaa instreq

LOOP
instreq_id =rec.instreq_id;
RETURN NEXT;
End Loop;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100
  ROWS 1000;

On Mon, Oct 12, 2009 at 4:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Balazs Klein" <Balazs.Klein@gmail.com> writes:
> > On 8.4.1 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.
>
> There's really no way to investigate that without a *complete* example.
>
>                        regards, tom lane
>

Balazs Klein <balazs.klein@gmail.com> writes:
> CREATE OR REPLACE FUNCTION instreq(OUT instreq_id integer)
> ...
> Select instreq.instreq_id From aaa instreq

The problem that you've got here is that instreq.instreq_id is in fact
a qualified reference to the function's OUT parameter.  We didn't
support qualification of function parameter names in 8.1, but we do now.

In general, I'd say that this is bad coding style.  Not only do you have
an ambiguous use of "instreq_id" (is it a column reference or a
parameter reference?), but the name "instreq" *also* has multiple
possible referents in this function.  You'll save yourself a lot of
headaches if you avoid that sort of thing.

            regards, tom lane

I still don't know which is the bit that is syntactically not correct
 > that a column name is a qualified reference to the function's OUT
parameter or
 > that the function name is also a table alias
and the error message is not very helpful to find this out.

But I certainly l learned that I shouldn't do this anyway.
Thanks and regards.
Balazs


On Tue, Oct 13, 2009 at 3:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Balazs Klein <balazs.klein@gmail.com> writes:
> > CREATE OR REPLACE FUNCTION instreq(OUT instreq_id integer)
> > ...
> > Select instreq.instreq_id From aaa instreq
>
> The problem that you've got here is that instreq.instreq_id is in fact
> a qualified reference to the function's OUT parameter.  We didn't
> support qualification of function parameter names in 8.1, but we do now.
>
> In general, I'd say that this is bad coding style.  Not only do you have
> an ambiguous use of "instreq_id" (is it a column reference or a
> parameter reference?), but the name "instreq" *also* has multiple
> possible referents in this function.  You'll save yourself a lot of
> headaches if you avoid that sort of thing.
>
>                        regards, tom lane
>