PostgreSQL Enums for Fun and Profit

Once in a while I find the need to reinvent the wheel when programming, when working on some project recently, the wheel happened to be syslog. Nothing wrong with syslog, but I felt the need to write my own logging to a table in a Postgres database. While doing so I found a great way for deciding whether an event can be logged at a certain log level using an enumerated datatype in Postgres. First we define the log levels (severities) as an Enum:

1
2
3
4
5
6
    CREATE TYPE eventseverity AS ENUM (
        'debug',
        'notice',
        'warn',
        'error'
    );

Every event in the database will have a severity assigned to it. Now, to decide whether a event can be logged when a certain threshold is set, we can make use of this enumerated datatype:

1
2
3
4
5
    SELECT enum_range(
        log_threshold::eventseverity,
        enum_last(null::eventseverity)) @> '{notice}'
    FROM eventconfig
    ;

Where {notice} is the current log level to be tested. This returns a boolean that tells whether the tested log level is above or below the threshold. This can of course be integrated into more complicated queries. For example in my project this does a boolean OR over rows returned for different thresholds.

Comments