I'm searching my ass of so you're my last hope. The task is quite simple:
I want to execute a trigger for each statement attached to a parent table (parent of partition tables) in Postgres 10.1. The triggered function will create a partition in case the corresponding range does not yet exist. The following function does not yet work for multiple years, I know ;).
Now my problem is, that I have no clue how to access the new data. When creating FOR EACH ROW, I have a variable 'NEW' (not allowed for partitions). When using INSTEAD OF INSERT, I should have something like 'inserted' as variable (not allowed for tables). So how can I access the new rows when using BEFORE INSERT FOR EACH STATEMENT? I just want all unique years which would be inserted afterwards.
CREATE TABLE ts AS ( id BIGSERIAL NOT NULL, start_ts TIMESTAMPTZ NOT NULL );
CREATE TABLE ts_2014 PARTITION OF ts FOR VALUES FROM ('2014-01-01') TO ('2015-01-01');
CREATE OR REPLACE FUNCTION create_ts_partition() RETURNS TRIGGER AS $$ DECLARE row_year INT; dst_partition_name VARCHAR; partition_found BOOLEAN; lower_bound VARCHAR; upper_bound VARCHAR; BEGIN row_year = ( SELECT extract(YEAR FROM start_ts) FROM NEW LIMIT 1); dst_partition_name = tg_table_name || '_' || row_year; partition_found = ( SELECT exists(pg_c1.relname) FROM pg_class AS pg_c1 WHERE pg_c1.relname LIKE dst_partition_name ); IF NOT partition_found THEN lower_bound = row_year || '-01-01'; upper_bound = (row_year + 1) || '-01-01'; EXECUTE 'CREATE TABLE ' || dst_partition_name || ' [...]'; END IF; END; $$ LANGUAGE 'plpgsql' PARALLEL UNSAFE;
CREATE TRIGGER ts_partition_creation BEFORE INSERT ON ts FOR EACH STATEMENT EXECUTE PROCEDURE create_ts_partition();
Thanks in advance. Kenneth
-- edit Thanks to Daniel Vérité, I've encountered some inconsistencies with the names inside the example. Thanks!