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
You could possibly use
currval() to get the last value from the sequence. or use
nextval() to get the id before the insert and then insert that value explicitly (instead of allowing the default value).
The third option is to ignore that column and instead use a natural key. That you are able to get the ID by doing a select suggests that there is a natural key for this table.
Lastval and currval are private to your connection, so must be issued on the same connection that issued the insert.