Iterating through individual fields of OLD/NEW records in plpgsql trigger
От | j.sachanbinski@coroplast.de |
---|---|
Тема | Iterating through individual fields of OLD/NEW records in plpgsql trigger |
Дата | |
Msg-id | OFDE3D7104.88EA59C5-ONC1256D2F.004B99DF@coroplast.de обсуждение исходный текст |
Ответы |
Re: Iterating through individual fields of OLD/NEW
Re: Iterating through individual fields of OLD/NEW records in plpgsql trigger |
Список | 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
В списке pgsql-general по дате отправления: