MIME-Version: 1.0
From: Kirk Parker <khp@equatoria.us>
Date: Tue, 23 Sep 2025 09:25:08 -0700
Message-ID: 
 <CANwZ8rkXSFCBOXW8mZ37vYo_MUe-Q35AOTX8uEjEZ2sRwqRohw@mail.gmail.com>
Subject: DDL Partitionion Inheritance -- improved trigger function
To: pgsql-docs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d1c88c063f7a61b1"
Archived-At: 
 <https://www.postgresql.org/message-id/CANwZ8rkXSFCBOXW8mZ37vYo_MUe-Q35AOTX8uEjEZ2sRwqRohw%40mail.gmail.com>
Precedence: bulk

--000000000000d1c88c063f7a61b1
Content-Type: text/plain; charset="UTF-8"

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.

--000000000000d1c88c063f7a61b1
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>I&#39;m a big fan of maintenance-fre=
e functions.=C2=A0 What would you think about adding the following as an al=
ternative trigger function, or as a replacement for the current function, t=
o=C2=A0</div><a href=3D"https://www.postgresql.org/docs/current/ddl-partiti=
oning.html#DDL-PARTITIONING-INHERITANCE-EXAMPLE" target=3D"_blank">https://=
www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-INHE=
RITANCE-EXAMPLE</a> , item #5?=C2=A0=C2=A0<div><br></div><blockquote style=
=3D"margin:0 0 0 40px;border:none;padding:0px"><div>CREATE OR REPLACE FUNCT=
ION measurement_insert_trigger()</div><div>RETURNS TRIGGER AS $$</div><div>=
BEGIN</div><div>=C2=A0 =C2=A0 EXECUTE format(&#39;INSERT INTO measurement_%=
s VALUES (NEW.*)&#39;, to_char( NEW.logdate, &#39;YYYYMM&#39;));</div><div>=
=C2=A0 =C2=A0 RETURN NULL;</div><div>END;</div><div>$$</div><div>LANGUAGE p=
lpgsql;</div><div><br></div></blockquote>For the modest overhead of an extr=
a call to to_char() and using EXECUTE rather than a literal INSERT, you get=
 a trigger function=C2=A0that works forever. Given that the example anticip=
ates one insert per city/day, it doesn&#39;t expect an extremely high rate =
of inserts where every microsecond counts.<div><br></div><div>And yes, bad =
things happen if the partition table does not exist, but that&#39;s true of=
 the other trigger functions shown here, too.</div><div><br><blockquote sty=
le=3D"margin:0 0 0 40px;border:none;padding:0px"><div><br></div></blockquot=
e></div></div>
</div>

--000000000000d1c88c063f7a61b1--