Re: Confusion in section 8.7.3. Type Safety

[Dejan Spasic]

Yes, you are right. Thanks for the fast feedback.

BitWeise
Erkrather Straße 304
D-40231 Düsseldorf
Tel.: +49.151 2915 88 99
E-Mail: dejan.spa...@bitweise.biz
WWW: http://www.bitweise.biz

On Tue, Sep 23, 2025, 1:14 PM David Rowley  wrote:

> On Tue, 23 Sept 2025 at 20:59, PG Doc comments form
>  wrote:
> > Page: https://www.postgresql.org/docs/17/datatype-enum.html
> > Description:
> >
> > In section 8.7.3. Type Safety one can observe a the following statement
> in
> > the examples.
> >
> > INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
> >
> > This is somewhat confusing since type happiness doesn't contain 'sad'. I
> > would suggest to remove the statement or to add an enum 'sad' in type
> > happiness.
>
> Thank you for the report. I think you might have missed that this
> section is demonstrating that the statement does not work due to the
> column's type not containing an enum value for 'sad' and that enum
> values are specific to the particular enum, rather than global to all
> enum types, as one *could* have assumed.
>
> Your proposed modification would make the bogus INSERT statement work,
> which would defeat the purpose of the section demonstrating that it
> doesn't work.
>
> David
>

DDL Partitionion Inheritance -- improved trigger function

[Kirk Parker]

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, 'MM'));
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.