public inbox for [email protected]  

help / color / mirror / Atom feed

From: Kirk Parker <[email protected]>
To: [email protected]
Subject: DDL Partitionion Inheritance -- improved trigger function
Date: Tue, 23 Sep 2025 09:25:08 -0700
Message-ID: <CANwZ8rkXSFCBOXW8mZ37vYo_MUe-Q35AOTX8uEjEZ2sRwqRohw@mail.gmail.com> (raw)

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.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: DDL Partitionion Inheritance -- improved trigger function
  In-Reply-To: <CANwZ8rkXSFCBOXW8mZ37vYo_MUe-Q35AOTX8uEjEZ2sRwqRohw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox