Aktualisierungs-Timestamps

Oft besteht der Bedarf, in einer Tabelle Zeitpunkt der Anlage eines Datensatzes sowie der letzten Änderung identifizieren zu können. Der einfachste Weg ist, in einer Tabelle zwei Timestamp-Spalten anzulegen, eine für den Anlagezeitpunkt und einen für die letzte Änderung.

Über den Default-Wert einer Spalte lässt sich automatisch der Anlagezeitpunkt in den Datensatz schreiben.

Beim Zeitpunkt der letzten Änderung ist das etwas schwieriger – dafür muss man zu einem Trigger greifen.

 

Tabelle anlegen

CREATE TABLE mytable (
  id           SERIAL,
  description  TEXT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
  updated_at   TIMESTAMPTZ
);

Die Spalte created_at wird durch die Angabe eines DEFAULT-Wertes (nur!) beim Anlegen eines Datensatzes auf den jeweiligen jetzigen Zeitpunkt gesetzt. Die Spalte updated_at bleibt erst einmal leer.

Trigger anlegen

In PostgreSQL besteht ein Trigger üblicherweise aus zwei Teilen: einer Trigger-Funktion, die etwas macht, und dem eigentlichen Trigger, der auf Änderungen in der Tabelle achtet und dann die Trigger-Funktion aufruft.

Hier der eigentliche Trigger. Dieser Trigger wird beim Ändern eines Datensatzes gefeuert (genauer: vor dem Festschreiben der Änderung). Es wird eine Trigger-Funktion set_updated_timestamp aufgerufen. Diese schreiben wir im nächsten Schritt.

CREATE TRIGGER tub_mytable_timestamp
BEFORE UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE set_updated_timestamp();

Die Trigger-Funktion:

CREATE OR REPLACE FUNCTION set_updated_timestamp()
RETURNS trigger
LANGUAGE plpgsql AS
$BODY$
BEGIN
  IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$BODY$;

 Es ist wichtig, dass diese Funktion mit möglichst allen Tabellen arbeiten kann. Deshalb muss die Zeitstempel-Spalte in jeder betroffenen Tabelle UPDATED_AT heißen. Die Funktion prüft, ob der aktuelle Datensatz (dessen Änderung den Aufruf dieser Funktion getriggert hat) sich nach der Änderung inhaltlich unterscheidet, d.h. ob also auch wirklich eine Änderung festgeschrieben worden ist. Falls ja, wird die Aktualisierungspalte auf den jetzigen Zeitstempel gesetzt.

Diese Trigger-Funktion set_updated_timestamp kann jetzt für alle Tabellen verwendet werden, sofern diese über eine Zeitstempel-Spalte mit dem Namen UPDATED_AT verfügen. Es muss ledliglich ein entsprechender Trigger (CREATE TRIGGER) angelegt werden.