Личный блог с мыслями и наблюдениями

Логирование изменений в Postgresql

Иногда нужно добавить логирование изменений в некоторых таблицах, для этого приходится городить сложную систему, которая будет отслеживать изменения и записывать в нужное место что поменялось, и есть большой риск забыть добавить логирование в каком-то месте, эту проблему можно изящно решить сделав триггер на изменение таблицы.

Создаем таблицу, в которой будут храниться изменения:

CREATE TABLE history (
    id                  serial,
    tstamp              timestamp       DEFAULT now(),
    schemaname      text,
    tabname         text,
    operation           text,
    who                 text            DEFAULT current_user,
    new_val             json,
    old_val             json,
        item_id             int8
);

Создаем функцию, которая будет записывать изменения:

CREATE OR REPLACE FUNCTION change_trigger() RETURNS trigger AS $$
        BEGIN
        IF      TG_OP = 'INSERT'
                THEN
                        INSERT INTO history (tabname, schemaname, operation, new_val, item_id)
                                        VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), NEW.id);
                                        RETURN NEW;
                        ELSIF   TG_OP = 'UPDATE'
                THEN
                        INSERT INTO history (tabname, schemaname, operation, new_val, old_val, item_id)
                                        VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
                                                        row_to_json(NEW), row_to_json(OLD), NEW.id);
                                                RETURN NEW;
                        ELSIF   TG_OP = 'DELETE'
                THEN
                        INSERT INTO history (tabname, schemaname, operation, old_val, item_id)
                                        VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD), OLD.id);
                                        RETURN OLD;
                        END IF;
        END;
        $$ LANGUAGE 'plpgsql' SECURITY DEFINER;

Вешаем триггер на нужные таблицы:

CREATE TRIGGER tablename_trigger BEFORE INSERT OR UPDATE OR DELETE ON tablename FOR EACH ROW EXECUTE PROCEDURE change_trigger();