We have a table
documents that is partitioned by time into child tables like
documents_2019_02. We do this using a
BEFORE trigger on the parent
documents table to insert into the correct child table and then return
NULL to abort the pipeline so it is only inserted into the child table, though (you guessed it), this makes inserting documents a pain as you have to re-query for the created ids. We've looked into using an
INSTEAD OF trigger type, but they cannot be applied to tables, only views.
Here is our current function used by the trigger:
CREATE OR REPLACE FUNCTION create_article_partition_and_insert() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE partition_date TEXT; partition TEXT; resultId BIGINT; BEGIN partition_date := to_char(NEW.updated, 'YYYY_MM'); partition := TG_RELNAME || '_' || partition_date; IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition) THEN RAISE NOTICE 'A partition has been created %',partition; EXECUTE 'CREATE TABLE ' || partition || ' (LIKE ' || TG_RELNAME || ' INCLUDING ALL) INHERITS (' || TG_RELNAME || '); '; END IF; RAISE NOTICE 'Inserting into %',partition; EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING id;' INTO resultId; NEW.id := resultId; RETURN NEW; END; $$;
Is there a better way to partition inserts by time and return the inserted row of the child table without also inserting into the parent table?
Using PG version