Postgresql – How to access data inside a “before insert trigger” for every statement

postgresqlpostgresql-10trigger

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!

Best Answer

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.

Related Question