From: | j(dot)sachanbinski(at)coroplast(dot)de |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Iterating through individual fields of OLD/NEW records in plpgsql trigger |
Date: | 2003-05-23 14:34:58 |
Message-ID: | OFDE3D7104.88EA59C5-ONC1256D2F.004B99DF@coroplast.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello everyone,
is there a way to loop/iterate through every individual field of the
OLD/NEW records in plpgsql trigger function?
I'm attempting to write a function that logs all the changes the user makes
to the table, sth. like: (sketch only)
CREATE TABLE data (
id integer,
value integer
);
CREATE TABLE log (
id integer,
changes text,
user text,
timestamp timestamptz
);
CREATE FUNCTION log_changes RETURNS trigger AS '
DECLARE
changes text;
BEGIN
changes := '''';
IF OLD.value <> NEW.value THEN
changes := changes || '' field value changed from: '' ||
OLD.value || ''to: '' NEW.value;
END IF;
INSERT INTO log VALUES (NEW.id, changes, session_user,
current_timestamp);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER log_changes BEFORE UPDATE ON data FOR EACH ROW EXECUTE
PROCEDURE log_changes();
Whilst this works as expected the function is completely table-dependent.
I would like to make it more generic by comparing all attributes of OLD and
NEW in a loop, sth. like
FOR field IN ??? LOOP
IF OLD.field <> NEW.field THEN
...
END IF;
END LOOP;
Combined with TG_RELNAME this could make such function reusable across
entire database.
Thanks in advance!
Jacek Sachanbinski
From | Date | Subject | |
---|---|---|---|
Next Message | alex b. | 2003-05-23 14:47:39 | Re: caching query results |
Previous Message | Neil Zanella | 2003-05-23 12:25:12 | network problems: -h flag not working properly |