Set up automatic updating of tables with updated columns

Finally: a legitimate use for triggers!

Many of your table definitions likely have these two columns:

create table users (
    name text not null,
-- ... other columns ...
    created timestamp with time zone not null default now(),
    updated timestamp with time zone not null default now());

create table customers (
    name text not null,
-- ... other columns ...
    created timestamp with time zone not null default now(),
    updated timestamp with time zone not null default now());

But how to you ensure that every update to a row in this table actually updates the "updated" column? A trigger, that's how:

-- Define this stored procedure once

create or replace function set_updated_column_to_now()
returns trigger as
$body$
begin
    NEW.updated = now();
    return NEW;  -- allow the row through for the actual update
end;
$body$
language plpgsql
volatile;

-- Define a trigger for each table that has an updated column

-- (The PostgreSQL docs say that multiple triggers on the same
-- table are executed in alphabetical order, so these are named
-- with a leading aaa_ on the assumption that we want them to
-- set the updated column before other triggers operate on the
-- row.)

create trigger aaa_set_users_updated_column_to_now
before update
on users
for each row
when (NEW.updated = OLD.updated)
execute procedure set_updated_column_to_now();

create trigger aaa_set_customers_updated_column_to_now
before update
on customers
for each row
when (NEW.updated = OLD.updated)
execute procedure set_updated_column_to_now();