How to access data inside a "before insert trigger" for every statement

Question

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 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!

Answer 1

The doc for CREATE TRIGGER in PostgreSQL 10 says:

Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views

Assuming nvme_ts is a view, you're trying to define a FOR EACH STATEMENT trigger in a case that the doc explicitly disallows.

Besides, the code shown in create_ts_partition() is meant for a single row, not for multiple rows with potentially different years. You probably simply need for a FOR EACH ROW trigger, if you're set on using a trigger at all to manage the partitions.

• K. May: The quote you've mentioned adds to the reasons why I can't use this ;). Sadly nvme_ts is no view but a table (apparently multiple names had sneak into the code. I'll change this.