PostgreSQL inter-related table partitioning

partitioning.sql

CREATE TABLE testsummary
(
   rowid serial NOT NULL,
   rundate date,
   data text,
   PRIMARY KEY (rowid)
);

CREATE TABLE testdetail
(
   rowid serial NOT NULL,
   parentid int NOT NULL,
   moredata text,
   PRIMARY KEY (rowid)
);

CREATE OR REPLACE FUNCTION testsummary_date(integer)
RETURNS date
AS 'SELECT rundate FROM testsummary WHERE testsummary.rowid = $1;'
LANGUAGE SQL;

BEGIN;
CREATE OR REPLACE FUNCTION add_partition_testsummary(tablename text, year char(4), month char(2))
RETURNS void
AS $$
DECLARE
    sql text;
    datestart text;
BEGIN
    datestart := (year || '-' || month || '-01');
    sql := 'CREATE TABLE ' || tablename || ' (CONSTRAINT ' || tablename || '_pkey PRIMARY KEY (rowid), CONSTRAINT ' || tablename || '_check CHECK ( rundate >= ''' || datestart || '''::date AND rundate < (''' || datestart || '''::date + interval ''1 month'') ) ) INHERITS (testsummary);';
    EXECUTE sql;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp;
REVOKE ALL ON FUNCTION add_partition_testsummary(tablename text, year char(4), month char(2)) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION add_partition_testsummary(tablename text, year char(4), month char(2)) TO some_user;
COMMIT;

BEGIN;
CREATE OR REPLACE FUNCTION add_partition_testdetail(tablename text, year char(4), month char(2))
RETURNS void
AS $$
DECLARE
    sql text;
    datestart text;
BEGIN
    datestart := (year || '-' || month || '-01');
    sql := 'CREATE TABLE ' || tablename || ' (CONSTRAINT ' || tablename || '_pkey PRIMARY KEY (rowid), CONSTRAINT ' || tablename || '_check CHECK (testsummary_date(parentid) >= ''' || datestart || '''::date AND testsummary_date(parentid) < (''' || datestart || '''::date + interval ''1 month'') ) ) INHERITS (testdetail);';
    EXECUTE sql;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp;
REVOKE ALL ON FUNCTION add_partition_testdetail(tablename text, year char(4), month char(2)) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION add_partition_testdetail(tablename text, year char(4), month char(2)) TO some_user;
COMMIT;

CREATE OR REPLACE FUNCTION public.testsummary_insert_trigger()
 RETURNS trigger
AS $function$
DECLARE
    year char(4);
    month char(2);
    tablename text;
    sql text;
BEGIN
    year := to_char(NEW.rundate, 'YYYY');
    month := to_char(NEW.rundate, 'MM');
    tablename := 'testsummary_y' || year || 'm' || month;
    sql := 'INSERT INTO ' || quote_ident(tablename) || ' SELECT ($1).*';

    IF NOT EXISTS(
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND   table_name = tablename
    ) THEN
        PERFORM add_partition_testsummary(tablename, year, month);
    END IF;
    EXECUTE sql USING NEW;

    RETURN NULL;
END;
$function$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.testdetail_insert_trigger()
 RETURNS trigger
AS $function$
DECLARE
    rundate date;
    year char(4);
    month char(2);
    tablename text;
    sql text;
BEGIN
    rundate := testsummary_date(NEW.parentid);
    year := to_char(rundate, 'YYYY');
    month := to_char(rundate, 'MM');
    tablename := 'testdetail_y' || year || 'm' || month;
    sql := 'INSERT INTO ' || quote_ident(tablename) || ' SELECT ($1).*';

    IF NOT EXISTS(
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
        AND   table_name = tablename
    ) THEN
        PERFORM add_partition_testdetail(tablename, year, month);
    END IF;
    EXECUTE sql USING NEW;

    RETURN NULL;
END;
$function$
LANGUAGE plpgsql;

CREATE TRIGGER testsummary_insert BEFORE INSERT ON testsummary FOR EACH ROW EXECUTE PROCEDURE testsummary_insert_trigger();
CREATE TRIGGER testdetail_insert BEFORE INSERT ON testdetail FOR EACH ROW EXECUTE PROCEDURE testdetail_insert_trigger();