clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799367 fiddles created (41678 in the last week).

create table events( starts date, eventtext varchar(20) );
 hidden batch(es)


insert into events(starts, eventtext) values ('2020-03-01', 'test1'), ('2020-03-01', 'test2')
2 rows affected
 hidden batch(es)


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;
 hidden batch(es)


select get_month_events(2020,03)
get_month_events
(9,2,,,,,,)
 hidden batch(es)