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 tbl (start date);
INSERT INTO tbl
SELECT generate_series(timestamp '2021-01-01', timestamp '2021-03-31', interval '1 day')
-- no data for April!
UNION ALL
SELECT generate_series(timestamp '2021-05-01', timestamp '2021-12-31', interval '1 day')
CREATE TABLE
INSERT 0 335
-- simple query with 1 row per month (I would use this)
SELECT to_char(mon, 'Mon') AS month, COALESCE(data.ct, 0) AS ct
FROM generate_series(date_trunc('month', LOCALTIMESTAMP) - interval '6 mon'
, date_trunc('month', LOCALTIMESTAMP) - interval '1 mon'
, interval '1 mon') mon
LEFT JOIN (
SELECT date_trunc('month', start) AS mon, count(*)::int AS ct
FROM tbl
GROUP BY 1
) data USING (mon)
ORDER BY mon;
| month | ct |
|---|---|
| Feb | 0 |
| Mar | 0 |
| Apr | 0 |
| May | 0 |
| Jun | 0 |
| Jul | 0 |
SELECT 6
-- proof of concept ...
CREATE OR REPLACE FUNCTION f_tbl_counts_6months(ANYELEMENT)
RETURNS SETOF ANYELEMENT
LANGUAGE plpgsql AS
$func$
DECLARE
_current_mon timestamp := date_trunc('month', LOCALTIMESTAMP);
BEGIN
-- to prevent incorrect column names, input row type must match current date:
IF right(pg_typeof($1)::text, 3) = to_char(_current_mon, 'mon') THEN
-- all good!
ELSE
RAISE EXCEPTION 'Current date is %. Function requires input >>%<<'
, CURRENT_DATE
, 'NULL::m6_' || to_char(now(), 'mon');
END IF;
RETURN QUERY
SELECT a[1], a[2], a[3], a[4], a[5], a[6]
FROM (
SELECT ARRAY(
SELECT COALESCE(data.ct, 0)
FROM generate_series(_current_mon - interval '6 mon'
, _current_mon - interval '1 mon'
, interval '1 mon') mon
LEFT JOIN (
SELECT date_trunc('month', start) AS mon, count(*)::int AS ct
FROM tbl
GROUP BY 1
) data USING (mon)
ORDER BY mon
)
) sub(a);
END
$func$;
CREATE FUNCTION
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
CREATE TYPE
-- Correct call for July (throws exception in other months)
SELECT * FROM f_tbl_counts_6months(NULL::m6_jul);
ERROR: Current date is 2024-08-03. Function requires input >>NULL::m6_aug<< CONTEXT: PL/pgSQL function f_tbl_counts_6months(anyelement) line 9 at RAISE
-- Correct call for November (throws exception in other months)
SELECT * FROM f_tbl_counts_6months(NULL::m6_nov);
ERROR: Current date is 2024-08-03. Function requires input >>NULL::m6_aug<< CONTEXT: PL/pgSQL function f_tbl_counts_6months(anyelement) line 9 at RAISE