I'm trying to create a function that returns all events with the corresponding week number, and the day of week the event is occurring using crosstab and series generator.
I have tested that the actual query works inside the funcition if I use literal values, e.g. 2020 and 3 (March month number) in place of the variables.
Here is the function query I'm trying to use:
CREATE OR REPLACE FUNCTION get_month_events( yr int, mth int, OUT week int, OUT sun int, OUT mon int, OUT tue int, OUT wed int, OUT thu int, OUT fri int, OUT sat int ) RETURNS SETOF RECORD AS $$ BEGIN RETURN QUERY SELECT * FROM crosstab(' SELECT extract(week from starts) as week, extract(dow from starts) as dow, count(*) FROM events WHERE extract(month from starts) = mth AND extract(year from starts) = yr GROUP BY week, dow ORDER BY week, dow', 'SELECT * FROM generate_series(0,6) AS dow' ) AS ( week int, sun int, mon int, tue int, wed int, thu int, fri int, sat int ) ORDER BY week; END; $$ LANGUAGE plpgsql;
When I try to call the function in a query, for example
SELECT * FROM get_month_events(2019, 8);
I'm getting this error:
ERROR: column "mth" does not exist LINE 7: WHERE extract(month from starts) = mth ^ QUERY: SELECT extract(week from starts) as week, extract(dow from starts) as dow, count(*) FROM events WHERE extract(month from starts) = mth AND extract(year from starts) = yr GROUP BY week, dow ORDER BY week, dow CONTEXT: PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY
Postgres does not recognize the parameter name inside the function query. How can I get it to reach the variable value?
It seems like it is just a stupid mistake I haven't spotted but so far I haven't been able to figure out why it does not let me access the variable.