Postgres is returning the “column reference '' is ambiguous” message inside a function even with aliases I have a function like this:

CREATE OR REPLACE FUNCTION test() RETURNS 
TABLE(my_date date, gates smallint, area_type character(1), 
      area_sizes float[], area_canvases float[]) AS $$
BEGIN
   return query 
   select coalesce(s.my_date, r.my_date) as my_date,  coalesce(s.gates, r.gates) as gates, coalesce(s.area_type, r.area_type) as area_type, s.area_sizes, r.area_canvases
   from (select my_date, gates, area_type, area_sizes from table1 where area_type = 'X') s
   full join (select my_date, gates, area_type, area_canvases from table2 where area_type = 'Y') r
   on r.my_date = s.my_date;
END $$ language plpgsql;


When I run it with select * from test(); what I get is:


  ERROR:  column reference "my_date" is ambiguous
  LINE 4:        my_date,


The query if not wrapped inside the function works properly, but I cannot make it work inside it. Any idea?
The planner cannot understand which my_date you are mentioning there. Either the one in parameters or the one in the table1 (or table2). The correct syntax should be like the one below; 

CREATE OR REPLACE FUNCTION test ()
RETURNS TABLE (
  my_date date, gates smallint, area_type character (1), 
  area_sizes float[], area_canvases float[]
)
AS $$
BEGIN
    RETURN query
    SELECT
        coalesce(s.my_date, r.my_date) AS my_date,
        coalesce(s.gates, r.gates) AS gates,
        coalesce(s.area_type, r.area_type) AS area_type,
        s.area_sizes,
        r.area_canvases
    FROM (
        SELECT
            t1.my_date,
            t1.gates,
            t1.area_type,
            t1.area_sizes
        FROM
            table1 t1
        WHERE
            t1.area_type = 'X') s
    FULL JOIN (
        SELECT
            t2.my_date, 
            t2.gates, 
            t2.area_type, 
            t2.area_canvases
        FROM
            table2 t2
        WHERE
            t2.area_type = 'Y') r ON r.my_date = s.my_date;
END
$$
LANGUAGE plpgsql;