CODEWITHSUNDEEP
postgresqlpostgresql-9.6
WSK
WSK

Reputation: 6158

Why I am getting "column reference *** is ambiguous"?

This query runs perfectly well on postgress and returns 2 columns that I am looking for:

SELECT  w.jobnr, w.ordernr
FROM
    (SELECT jobnr, ordernr FROM userdata.WIP_Data WHERE Year=2015 AND period=1) AS W
    LEFT OUTER JOIN
    (SELECT jobnr, ordernr FROM userdata.WIP_Data WHERE Year=2015 AND period=2) AS P 
    ON W.Jobnr=P.Jobnr;

It returns:
enter image description here

But when I enclose this query in a function as under:

CREATE OR REPLACE FUNCTION userdata.test3()
 RETURNS TABLE(jobnr character varying, ordernr character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT  w.jobnr, w.ordernr
    FROM
        (SELECT jobnr, ordernr FROM userdata.WIP_Data WHERE Year=2015 AND period=1) AS W

        LEFT OUTER JOIN
        (SELECT jobnr, ordernr FROM userdata.WIP_Data WHERE Year=2015 AND period=2) AS P 
        ON W.Jobnr=P.Jobnr;
END; 
$function$

and execute it by SELECT * from userdata.test3() I am getting following error:

ErrorCode: -2147467259
Severity: ERROR, Code: 42702, Line: 1076, Position:
ErrorMessage: column reference "jobnr" is ambiguous
Detail: It could refer to either a PL/pgSQL variable or a table column.

Any idea what is wrong here and how can I resolve it? Thanks

Upvotes: 8

Views: 10390

Answers (3)

Andreas
Andreas

Reputation: 5103

From the documentation,

38.5.10. SQL Functions Returning TABLE

There is another way to declare a function as returning a set, which is to use the syntax RETURNS TABLE(columns). This is equivalent to using one or more OUT parameters plus marking the function as returning SETOF record (or SETOF a single output parameter's type, as appropriate). This notation is specified in recent versions of the SQL standard, and thus may be more portable than using SETOF.

That means when you declare the function with RETURNS TABLE(jobnr character varying..., jobnr is an out parameter. Thus SELECT jobnr ... is ambiguous.

Try declaring the function with aliases for the tables in your select:

CREATE OR REPLACE FUNCTION userdata.test3()
 RETURNS TABLE(jobnr character varying, ordernr character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT  w.jobnr, w.ordernr
    FROM
        (SELECT wip.jobnr, wip.ordernr FROM userdata.WIP_Data as wip WHERE Year=2015 AND period=1) AS W

        LEFT OUTER JOIN
        (SELECT wip.jobnr, wip.ordernr FROM userdata.WIP_Data as wip WHERE Year=2015 AND period=2) AS P 
        ON W.Jobnr=P.Jobnr;
END; 
$function$

Upvotes: 13

Binary Enigma
Binary Enigma

Reputation: 58

For anyone who, like me, did not have the option to use table identifiers to distinguish columns from the ones in the RETURNS clause, switching the function's LANGUAGE from plpgsql to sql (and adapting the queries to that change) removed this ""feature"".

In my specific case, the conflict was with the express naming of columns on a RETURNING after an INSERT

I would like a solution that works while staying in plpgsql, but have not found it.

Upvotes: 2

Emilio Platzer
Emilio Platzer

Reputation: 2469

May be a bug confusing the returning clausule

 RETURNS TABLE(jobnr character varying, ordernr character varying)

with a parameter declaration.

I know than jobnr is not a parameter (it is a column name of the returned recordset). But aperars to be that Postgres is confused about it.

Try it with others names in the RETURNS clausule

Is not a bug, it's a feature

@Andreas is right! But this is a nonsense feature.

Upvotes: 2

Related Questions