декларативно партиционированные креативы

CREATE TABLE creatives (
    id          bigserial   NOT NULL,
    dsp_id      INTEGER     NOT NULL,
    bid_id      text        NOT NULL, -- тащемта явно не все креативы в мире имеют это поле... но пока что openrtb, а там посмотрим
    click_url   text        NOT NULL,
    title       text        NOT NULL,
    thumbs      text[][]    NOT NULL,
    dump        jsonb       NOT NULL,
    ctime       TIMESTAMP   NOT NULL DEFAULT now(),
    info        json
) PARTITION BY RANGE (ctime);

CREATE OR REPLACE FUNCTION week_table_suffix_from_ts(ts TIMESTAMP WITH TIME zone) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    table_suffix text := REPLACE(SUBSTRING((date_trunc('week', ts))::text FROM 1 FOR 10),'-','');
BEGIN
    RETURN table_suffix;
END;
$$;

CREATE OR REPLACE FUNCTION creatives_partitioning_trigger_f() RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $$
DECLARE
    table_prefix    text    := 'creatives_';
    CURRENT_TIME    TIMESTAMP WITH TIME zone := now();
    table_suffix    text    := week_table_suffix_from_ts(CURRENT_TIME);
    TABLE_NAME      text    := 'creatives_' || table_suffix;
    table_exists    BOOLEAN;
BEGIN
    EXECUTE
        'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = $1 AND tablename = $2)'
        INTO table_exists
        USING 'public', TABLE_NAME;

    IF NOT table_exists THEN
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF creatives FOR VALUES FROM (%L) TO (%L)'::text,
            TABLE_NAME,
            date_trunc('week', CURRENT_TIME),
            date_trunc('week', CURRENT_TIME + INTERVAL '1 week')
        );

        EXECUTE format( -- это ж всё равно serial, поэтому unique не делаю
            'CREATE INDEX IF NOT EXISTS %I ON %I (id)'::text,
            TABLE_NAME || '_id', TABLE_NAME
        );

        EXECUTE format(
            'CREATE INDEX IF NOT EXISTS %I ON %I (bid_id)'::text,
            TABLE_NAME || '_bidid', TABLE_NAME
        );

        EXECUTE format(
            'CREATE INDEX IF NOT EXISTS %I ON %I (dsp_id, ctime)'::text,
            TABLE_NAME || '_dspid_ctime', TABLE_NAME
        );
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER creatives_partitioning_trigger BEFORE INSERT ON creatives FOR EACH STATEMENT EXECUTE PROCEDURE creatives_partitioning_trigger_f();