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?