CREATE OR REPLACE FUNCTION public.utc_year(text)
  RETURNS integer AS
$BODY$
  SELECT date_part('year', cast($1 as timestamp) at time zone 'UTC')::integer
$BODY$
  LANGUAGE sql IMMUTABLE PARALLEL SAFE RETURNS NULL ON NULL INPUT
  COST 100;


CREATE OR REPLACE FUNCTION public.utc_month(text)
  RETURNS integer AS
$BODY$
  SELECT date_part('month', cast($1 as timestamp) at time zone 'UTC')::integer
$BODY$
  LANGUAGE sql IMMUTABLE PARALLEL SAFE RETURNS NULL ON NULL INPUT
  COST 100;


CREATE TABLE raw_master (
        id serial,
        document jsonb
) PARTITION BY LIST (utc_year(document ->> 'TIMESTAMP_UTC'));

CREATE LANGUAGE plpythonu;

DO
$$
    for year in xrange(2000, 2021):
        stmt = ("CREATE TABLE raw_{0} PARTITION OF raw_master FOR VALUES IN ({0}) "
                "PARTITION BY LIST (utc_month(document ->> 'TIMESTAMP_UTC'))"
                .format(year))
        plpy.execute(stmt)
$$
LANGUAGE plpythonu;


DO
$$
    for year in xrange(2000, 2021):
        for month in xrange(1, 13):
                stmt = ("CREATE TABLE raw_{0}_{1} PARTITION OF raw_{0} FOR VALUES IN ({1})"
                        .format(year, month))
                plpy.execute(stmt)
$$
LANGUAGE plpythonu;

SET constraint_exclusion = 'on';