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!