postgresql daily tables

gistfile1.sql

CREATE LANGUAGE plpgsql;

CREATE TABLE request_logs(
  id SERIAL PRIMARY KEY,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ev JSONB
);


CREATE OR REPLACE FUNCTION daily_table_setup(schema_name text,
                                             origin_table text,
                                             target_table text,
                                             start_time timestamp,
                                             end_time timestamp,
                                             ts timestamp)
RETURNS void AS
$BODY$
DECLARE
  rStmt text;
  idx_ts text;
BEGIN
  idx_ts := to_char(start_time, 'YYYYMMDD');
  -- don't forget to create all respective indexes. they are not inherited.
  EXECUTE format('CREATE TABLE %s.%s (
    CHECK (ts >= ''%I'' AND ts < ''%I'')
  ) INHERITS (%I.%I)', schema_name, target_table,
                       start_time, end_time,
                       schema_name, origin_table);
    EXECUTE format('CREATE INDEX idevtype_%s ON %I.%I USING gin (ev)', idx_ts, schema_name, target_table);
END;
$BODY$
LANGUAGE plpgsql;


-- set this up as a trigger to create daily tables
-- inputs:
-- schema_name
-- parent_table name
-- timestamp column name
-- function name used to create child tables
CREATE OR REPLACE FUNCTION autocreate_daily_table()
RETURNS TRIGGER AS $$
DECLARE
  schema_name text := TG_ARGV[0];
  origin_table text := TG_ARGV[1];
  ts_col text := TG_ARGV[2];
  create_fun regproc := TG_ARGV[3]::regproc;

  part_date text;
  full_table_name text;
  start_time timestamp;
  end_time timestamp;
  ts timestamp;
BEGIN
  EXECUTE format('SELECT ($1.%I)::timestamp', ts_col) INTO ts USING NEW;
  start_time := date_trunc('day', ts);
  end_time := date_trunc('day', (start_time + INTERVAL '1 DAY'));
  part_date := to_char(start_time, 'YYYYMMDD');
  full_table_name := format('%s_%s', origin_table, part_date);

  IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = schema_name AND table_name = full_table_name) THEN
    EXECUTE format('SELECT %I(%L, %L, %L, %L, %L, %L)',
                       create_fun,
                       schema_name,
                       origin_table,
                       full_table_name,
                       start_time,
                       end_time,
                       ts);
  END IF;

    EXECUTE format('INSERT INTO %I.%I VALUES($1.*)', schema_name, full_table_name) USING NEW;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER autocreate_log_tables_trg
BEFORE INSERT ON request_logs
FOR EACH ROW EXECUTE PROCEDURE autocreate_daily_table('public', 'request_logs', 'ts', 'daily_table_setup');