Add trigger to update last modified column in postgres

00_about.mkd

Automated last updated columns -- postgresql

SYNOPSIS

Create an updated_at timestamp column that is automatically updated whenever row values are changed in a postgresql database.

ABOUT

• Modifying a column when a row is changed requires a BEFORE UPDATE TRIGGER on row updates.
• A trigger can call a procedure
• A function can create a trigger programatically.

RELATED PROJECTS

I searched and found a variety of sources with a handful of suggestions and examples on StackOverflow, sql blogs, and similar. The code I found was OK at best, with redundancies and shortcomings.

That research taken together with the excellent postgresql docs and a fair amount of experimentation brought me to create the following snippets.

SOLUTION

For my uses, I want every updated_at column to have a trigger to update when the row is modified.

1. Use moddatetime for the trigger, which is distributed with postgresql.
   • moddatetime is documented in the spi section
2. A function to create the trigger for a table.
   • Takes the name of the table and optionally the name of the column, defaulting to updated_at
3. A query to apply the trigger to all tables containing the updated_at column.
   • Search for all tables in the current schema containing an updated_at column.

DETAILS AND CAVEATS

1. in the WHEN clause of the trigger I have used WHEN (OLD DISTINCT FROM NEW) this is subtely different and preferred to WHEN OLD <> NEW when comparing NULL columns.
   • The WHEN clause exists to prevent the timestamp from changing when a row is "updated" with the current fields and no net changes. If you consider that an update with tracking, then remove the WHEN clause completely.
2. The database schema is a separation of a single database by namespace. The default schema is public unless the client changes it for a connection. Database schema is entirely different from table schemas. Data and functions in other schemas can be accessed directly. Accessing data from other databases requires work and connectors.
3. Wrapping a query with multiple rows of output into a function is a pain.
4. DROP TRIGGER IF EXISTS vs CREATE OR REPLACE TRIGGER, the latter is added in Postgresql 14. My target database was still stuck on postgresql 13. https://www.postgresql.org/docs/current/sql-createtrigger.html

01_import moddatetime.sql

CREATE EXTENSION IF NOT EXISTS moddatetime;

02_add_updated_at_trigger.sql

CREATE OR REPLACE FUNCTION add_updated_at_trigger(
    tbl text
    , fld text DEFAULT 'updated_at') 
    RETURNS text AS $T1$
BEGIN
    EXECUTE format(
        'DROP TRIGGER IF EXISTS %1$s_%2$s ON %1$s;
        CREATE TRIGGER %1$s_%2$s
        BEFORE UPDATE ON %1$s
        FOR EACH ROW
        WHEN (OLD IS DISTINCT FROM NEW)
            EXECUTE PROCEDURE moddatetime (%2$s);'
        , tbl, fld);
    RETURN FORMAT('%s_%s' , tbl , fld) AS trigger_name;
    END
$T1$ LANGUAGE plpgsql
;

03_add trigger to all matching tables

SELECT tbl, add_updated_at_trigger(tbl)
FROM (
    SELECT table_name from information_schema.columns
    WHERE column_name = 'updated_at'
    AND table_schema <> current_schema
    ORDER BY table_name
    ) x(tbl)

Updated to add a return string from the function containing the name of the trigger.

psql> select add_updated_at_trigger('my_table');
 add_updated_at_trigger
------------------------
 my_table_updated_at
(1 row)

gogopixdb_schema_test2=> select add_updated_at_trigger('my_table','foo');
NOTICE:  trigger "my_table_foo" for relation "my_table" does not exist, skipping
 add_updated_at_trigger
------------------------
 my_table_foo
(1 row)

Also improved clarity of the FORMAT argument by using values by index rather than having to duplicate
the variable names. %s => %1$s. Positional arguments are numbered from 1.