Search code examples
postgresqlpostgresql-9.6

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

Solution

  • 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$