Postgres 13

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 TYPE m6_jan AS ("Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int);
CREATE TYPE m6_feb AS ("Aug" int, "Sep" int, "Oct" int, "Nov" int, "Dec" int, "Jan" int);
CREATE TYPE m6_mar AS ("Sep" int, "Oct" int, "Nov" int, "Dec" int, "Jan" int, "Feb" int);
CREATE TYPE m6_apr AS ("Oct" int, "Nov" int, "Dec" int, "Jan" int, "Feb" int, "Mar" int);
CREATE TYPE m6_may AS ("Nov" int, "Dec" int, "Jan" int, "Feb" int, "Mar" int, "Apr" int);
CREATE TYPE m6_jun AS ("Dec" int, "Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int);
CREATE TYPE m6_jul AS ("Jan" int, "Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int);
CREATE TYPE m6_aug AS ("Feb" int, "Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int);
CREATE TYPE m6_sep AS ("Mar" int, "Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int);
CREATE TYPE m6_oct AS ("Apr" int, "May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int);
CREATE TYPE m6_nov AS ("May" int, "Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int);
CREATE TYPE m6_dec AS ("Jun" int, "Jul" int, "Aug" int, "Sep" int, "Oct" int, "Nov" int);

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