Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

CREATE TABLE


insert into events(starts, eventtext) 
values
('2020-03-01', 'test1'),
('2020-03-01', 'test2')

INSERT 0 2


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;

CREATE FUNCTION


select get_month_events(2020,03)

ERROR:  function crosstab(text, unknown) does not exist
LINE 1: SELECT * FROM crosstab('
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
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
CONTEXT:  PL/pgSQL function get_month_events(integer,integer) line 3 at RETURN QUERY