0

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?

0

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;
  • Thank you it worked. The strange thing is that if I run the. query without function wrapping it works perfectly – Randomize Feb 25 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.