Trigger nur geänderte Spalten

Hallo in das Forum,

mein Name ist Frank und starte gerade meine ersten Schritte mit Postgres, und mit SQL :slight_smile:
Die ersten Schritte um meine ‘alten’ DBF zu konvertieren und meine Programme anzupassen sind bis jetzt recht erfolgreich. Nun bin ich bei Triggern gelandet und hänge da an einem Problem, wo ich keine Lösung finde.
Ich möchte ein Audit Trigger erstellen, wo ich nur die tatsächlichen Änderungen mitspeichere. Mein Vorgehen bisher ist bis jetzt, die Tabellenstrukture auslesen und jeden Inhalt in der NEW und OLD vergleichen. Ist dieser unterschiedlich, später ein Text bilden der gespeichert wird.
Wie kann ich z.B. anstatt:
NEW.COL1 != OLD.COL1
ersetzen im LOOP mit dem Spaltennamen aus columns?


CREATE OR REPLACE FUNCTION public.adressen_update()
RETURNS trigger AS
$BODY$
DECLARE
columns RECORD;
n char(500);
o char(500);

BEGIN


FOR columns IN select column_name from information_schema.columns where table_name=TG_TABLE_NAME::TEXT

LOOP
n=‘NEW.’||columns.column_name; /// hier ist mein Problem, ich möchte jeden Wert vergleichen, und diesen dann in changes speicher.
o=‘OLD.’||columns.column_name;
if (n != o)
THEN
INSERT INTO auditlog (tablename,username,creation,action,changes)
VALUES (TG_TABLE_NAME::TEXT,session_user::TEXT,now(),TG_OP,n);
END IF;
END loop;


RETURN NEW;


return NEW;

EXCEPTION
WHEN data_exception THEN
RAISE WARNING ‘[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %’,SQLSTATE,SQLERRM;
RETURN NULL;
WHEN unique_violation THEN
RAISE WARNING ‘[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %’,SQLSTATE,SQLERRM;
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING ‘[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %’,SQLSTATE,SQLERRM;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.adressen_update()
OWNER TO postgres;


Danke für eure Hilfe,
Frank

Wilkommen bei PostgreSQL!

Zu Deinem Problem: n und o enthalten einen Text, Du willst diesen Text quasi als SQL interpretieren und ausführen. Dazu dient EXECUTE (im Kontext einer Stored Proc führt EXECUTE einen dynamisch erstellten String aus) Damit sollte Dein Problem soweit lösbar sein.


Beachte bitte: Deine Löung wird die Last auf dem Server vervielfachen, da im TRIGGER nun für jede Zeile eine ganze Latte von Aktionen auszuführen sind, noch dazu in einer Schleife. Der View information_schema.columns basiert selbst auf einem recht komplexen SQL. Deine Abfrage auf diesen View beachtet auch nicht, daß ein und derselbe Tabellennamen mehrfach in unterschiedlichen Schemas auftreten kann.

Für solche Zwecke gibt es erprobte Lösungen:

https://www.pgaudit.org/
etwas älter: https://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html (suche dort mal in den Kommentaren, da hat ein gewisser akretschmer noch auf eine andere Lösung hingewiesen…)

Mit pglogical könntest Du auch noch eine weitere Variante realisieren, indem das Transaction Log als Basis des Auditlogs dient.

Du musst das Rad nicht neu erfinden, dafür gibt es diverse Lösungen:

Bei den JSONB basierten Lösungen wird noch ein “-” operator für JSONB benötigt: https://github.com/glynastill/pg_jsonb_delete_op

Hallo

vielen Dank für die ausführliche Antwort. Ich werde mir diese Links durchlesen und wohl doch lieber alles speichern, anstatt nur die geänderten Daten. Muss erstmal sehen welche Datenmenge da auf einen zukommen.

Grüße Frank

Danke für die Links
hab wohl nicht das richtige gesucht in Google :slight_smile:

Genug zum studieren, für die Lernkurve :slight_smile: