# 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](https://www.postgresql.org/docs/13/contrib-spi.html)
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