Cacher is the code snippet organizer for pro developers

--- The items table recieves rows and will trigger after insert statements
CREATE TABLE public.items (
	instant timestamp NULL,
	value text NULL,
	qualifier int4 NULL
);

--- And partitioned_items is the.. err... parent partition
CREATE TABLE public.partitioned_items (
	instant timestamp NULL,
	value text NULL,
	qualifier int4 NULL
) PARTITION BY RANGE(instant);


--- Get us some sample data to work with
CREATE MATERIALIZED VIEW public.materialized_samples
TABLESPACE pg_default
AS WITH day_table AS (
         SELECT row_number() OVER (ORDER BY value.value) AS id,
            value.value
           FROM generate_series('2000-01-01 00:00:00'::timestamp without time zone, '2000-01-05 00:00:00'::timestamp without time zone, '1 day'::interval) value(value)
        ), strings AS (
         SELECT generate_series(1, 10) AS id,
            md5(random()::text) AS value
        ), randoms AS (
         SELECT ceil(random() * (( SELECT max(day_table.id) AS max
                   FROM day_table))::double precision)::integer AS day_id,
            ceil(random() * 60::double precision) * '00:01:00'::interval AS interval_to_add,
            ceil(random() * 5::double precision)::integer AS qualifier,
            ceil(random() * (( SELECT max(strings.id) AS max
                   FROM strings))::double precision) AS string_id
           FROM generate_series(1, 100000) generate_series(generate_series)
        ), samples AS (
         SELECT day_table.value + randoms.interval_to_add AS instant,
            strings.value,
            randoms.qualifier
           FROM randoms
             LEFT JOIN day_table ON randoms.day_id = day_table.id
             LEFT JOIN strings ON randoms.string_id = strings.id::double precision
          ORDER BY (day_table.value + randoms.interval_to_add)
        )
 SELECT samples.instant,
    samples.value,
    samples.qualifier
   FROM samples
WITH DATA;

--- Keep track of partitions that have been created (for a specific table)
CREATE MATERIALIZED VIEW public.partitioned_items_meta
TABLESPACE pg_default
AS WITH RECURSIVE inh AS (
         SELECT i.inhrelid,
            NULL::text AS parent
           FROM pg_inherits i
             JOIN pg_class cl ON i.inhparent = cl.oid
             JOIN pg_namespace nsp ON cl.relnamespace = nsp.oid
          WHERE nsp.nspname = 'public'::name AND cl.relname = 'partitioned_items'::name
        UNION ALL
         SELECT i.inhrelid,
            i.inhparent::regclass::text AS inhparent
           FROM inh inh_1
             JOIN pg_inherits i ON inh_1.inhrelid = i.inhparent
        )
 SELECT n.nspname AS partition_schema,
    c.relname AS partition_name,
    daterange((ARRAY( SELECT array_to_string(regexp_matches(pg_get_expr(c.relpartbound, c.oid, true), '\d+-\d+-\d+'::text, 'g'::text), ''::text) AS array_to_string))[1]::date, (ARRAY( SELECT array_to_string(regexp_matches(pg_get_expr(c.relpartbound, c.oid, true), '\d+-\d+-\d+'::text, 'g'::text), ''::text) AS array_to_string))[2]::date) AS coverage
   FROM inh
     JOIN pg_class c ON inh.inhrelid = c.oid
     JOIN pg_namespace n ON c.relnamespace = n.oid
     LEFT JOIN pg_partitioned_table p ON p.partrelid = c.oid
  ORDER BY n.nspname, c.relname
WITH DATA;


--- Trigger function that:
---   * creates partitions on demand
---   * copies row into the parent table
---   * refreshes the matview of partitions
---   * "truncates" the source
CREATE OR REPLACE FUNCTION public.partition_items()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
	declare
		r record; dsql text;
	begin
		for r in select
			distinct date_trunc('day', i.instant) as range_start, date_trunc('day', i.instant + interval '1 day') as range_end 
		from
			items i
		left join 
			partitioned_items_meta p on p.coverage @> i.instant::date
		where
			p.coverage is null
		loop
			dsql := format('create table if not exists foo_%s partition of partitioned_items for values from (''%s'') to (''%s'')', to_char(r.range_start, 'YYYYMMDD'), r.range_start, r.range_end);
			raise notice 'q: %', dsql;
			execute dsql;
		end loop;
		insert into partitioned_items select * from items;
		refresh materialized view partitioned_items_meta;
		delete from items;
		return null;
	end
$function$;

--- Hook up the trigger
create
    trigger items_after_insert_stmt after insert
        on
        public.items for each statement execute procedure partition_items();
        
insert into items select * from materialized_samples;