Why should you care?

PostgresVerse updated_at column stays fresh on every row change without each API developer remembering to set it.

See it live — copy this example

Run in pgAdmin or psql.

CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_touch
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION touch_updated_at();

What happened?

• BEFORE UPDATE assigns NEW.updated_at to current time and returns NEW so update proceeds with fresh timestamp.
• Same function reused on many tables.

Practice next

1. Add updated_at timestamptz to products.
2. Create function and trigger.
3. UPDATE name only; verify updated_at changed.
4. Log OLD and NEW json to audit table in trigger function.
5. Use TG_OP to branch INSERT vs UPDATE logic.

Remember

RETURNS trigger and special variables OLD/NEW. RETURN NULL skips row change in BEFORE triggers. Share one function across multiple triggers.

PostgresVerse sync timestamps

Mobile cache uses updated_at for delta sync across products and orders.

Outcome: Cache invalidation works even for admin SQL fixes in pgAdmin.