title: Why I am getting "column reference *** is ambiguous"?
body: <p>This query runs perfectly well on postgress and returns 2 columns that I am looking for:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>It returns:<br>
  <a href="https://i.stack.imgur.com/KqNmN.png" rel="noreferrer"><img src="https://i.stack.imgur.com/KqNmN.png" alt="enter image description here"></a></p>
  
  <p>But when I enclose this query in a function as under:</p>
  
  <pre><code>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$
  </code></pre>
  
  <p>and execute it by <code>SELECT * from userdata.test3()</code>
  I am getting following error:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Any idea what is wrong here and how can I resolve it? Thanks</p>