DDL Partitionion Inheritance -- improved trigger function

From: Kirk Parker <khp(at)equatoria(dot)us>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: DDL Partitionion Inheritance -- improved trigger function
Date: 2025-09-23 16:25:08
Message-ID: CANwZ8rkXSFCBOXW8mZ37vYo_MUe-Q35AOTX8uEjEZ2sRwqRohw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-docs

I'm a big fan of maintenance-free functions. What would you think about
adding the following as an alternative trigger function, or as a
replacement for the current function, to
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHERITANCE-EXAMPLE
, item #5?

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE format('INSERT INTO measurement_%s VALUES (NEW.*)', to_char(
NEW.logdate, 'YYYYMM'));
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

For the modest overhead of an extra call to to_char() and using EXECUTE
rather than a literal INSERT, you get a trigger function that works
forever. Given that the example anticipates one insert per city/day, it
doesn't expect an extremely high rate of inserts where every microsecond
counts.

And yes, bad things happen if the partition table does not exist, but
that's true of the other trigger functions shown here, too.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message David Rowley 2025-09-23 23:30:07 Re: DDL Partitionion Inheritance -- improved trigger function
Previous Message Dejan Spasic 2025-09-23 13:35:32 Re: Confusion in section 8.7.3. Type Safety