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  (Larry Rosenman <ler@lerctr.org>)
Re: Iterating through individual fields of OLD/NEW records in plpgsql trigger  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: nzanella@cs.mun.ca (Neil Zanella)
Дата:
Сообщение: network problems: -h flag not working properly
Следующее
От: Daniel Carlsson
Дата:
Сообщение: Trigger in transaction