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.

Table:

CREATE TABLE ts AS (   id BIGSERIAL         NOT NULL,   start_ts TIMESTAMPTZ NOT NULL ); 

Partitions:

CREATE TABLE ts_2014   PARTITION OF ts    FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'); 

Trigger Function:

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; 

Trigger:

CREATE TRIGGER nvme_ts_partition_creation   INSTEAD OF INSERT   ON nvme_ts   FOR EACH STATEMENT EXECUTE PROCEDURE create_nvme_ts_partition(); 

Thanks in advance. Kenneth