Триггер для резервного копирования некоторых данных в PostgreSQL

У меня есть таблица SQL, которая автоматически заполнялась каким-то умным кодом. Однако были случаи, когда алгоритм не работал. Чтобы избежать оговорки о правиле 80/20, т.е. тратить 80% отведенного проектом времени менее чем на 20% задач, я решил решить оставшиеся вопросы вручную.

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

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

Решение

Давайте использовать таблицу, которая выглядит так:

CREATE TABLE data ( id SERIAL PRIMARY KEY, name CHARACTER VARIING);

Во-первых, я добавил новый столбец, чтобы отслеживать вставленные вручную строки:

ALTER TABLE data ADD COLUMN manually_created BOOLEAN;
UPDATE data SET manually_created = false;

После этого я создаю резервную таблицу с той же схемой, что и исходная:

CREATE TABLE data_backup LIKE data;

Хорошо, теперь мы можем думать о заполнении этой резервной таблицы.

Что такое триггеры и как они работают

Триггеры — очень удобная функция SQL. Они позволяют пользователю определить действие, которое должно быть выполнено в определенное время, когда событие запускается в базе данных. Поддерживаемые события: INSERT, DELETE, UPDATE и даже, для PostgreSQL, TRUNCATE. Похоже на то, что мы ищем: каждый раз, когда строка вставляется, удаляется или обновляется в нашей основной таблице, мы сможем сделать что-то еще, может быть, выполнить то же действие в нашей резервной таблице?

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

Внутри функции вы можете получить доступ к текущим данным строки: в зависимости от операции, NEW а также OLD объекты доступны. Например, в операторе обновления, изменяющем имя с «toto» на «tata», у нас будет NEW.name="tata" а также OLD.name="toto". Идеальный!

Пример в этом конкретном случае

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

Начнем с написания функции, которая будет выполнять это действие:

CREATE OR REPLACE FUNCTION backup_data_func() RETURNS trigger AS $rval$ 
BEGIN 
    IF (NEW.manually_created = true) THEN 
    -- remember we only want to backup the manually created rows 
        INSERT INTO data_backup(id, name, manually_created) 
        VALUES(NEW.id, NEW.name, NEW.manually_created); 
    END IF; 
    -- result is not important for AFTER triggers, like the one we will use
    RETURN NULL;  
END;
$rval$ LANGUAGE plpgsql;

Эта функция называется backup_data_func берет строку таблицы данных и просто вставляет те же данные в data_backup стол. Теперь мы можем привязать эту функцию к действию:

CREATE TRIGGER data_table_backup
AFTER INSERT ON data
FOR EACH ROW EXECUTE PROCEDURE backup_data_func();

Итак, наш триггер выполнит функцию backup_data_func для каждой затронутой строки после каждой INSERT заявление о data стол. Так просто

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

А что, если мы также хотим, чтобы данные резервной копии изменялись при обновлении или даже удалении строки в основной таблице? Для этого мы можем использовать другие специальные переменные, список которых определен здесь, в документе PG10. Интересно для нас вот TG_OPсообщая нам об операции, для которой сработал триггер. Давайте изменим нашу функцию, чтобы иметь дело с UPDATE а также DELETE заявления:

CREATE OR REPLACE FUNCTION backup_data_func() RETURNS trigger AS $rval$ 
BEGIN 
    IF (TG_OP = 'INSERT') THEN 
        IF (NEW.manually_created = true) THEN
        -- remember we only want to backup the manually created rows 
            INSERT INTO data_backup(id, name, manually_created) 
            VALUES(NEW.id, NEW.name, NEW.manually_created); 
        END IF; -- new things start here 
    ELSIF (TG_OP = 'UPDATE') THEN 
        IF (NEW.manually_created = true) THEN 
        -- still dealing ony with manually modified rows 
            UPDATE data_backup 
            SET id = NEW.id, name=NEW.name, manually_created=NEW.manually_created 
        END IF; 
    ELSIF (TG_OP = 'DELETE') THEN 
    -- our trigger calls this function AFTER the statement is executed, 
    -- so for the delete statement, we can only access the OLD instance! 
        IF (OLD.manually_created = true) THEN 
            DELETE FROM data_backup WHERE id = OLD.id; 
        END IF; 
    END IF; 
RETURN NULL; 
END;
$rval$ LANGUAGE plpgsql;

И мы также должны изменить наш триггер, чтобы функция выполнялась и после обновления:

CREATE TRIGGER data_table_backup
AFTER INSERT OR UPDATE OR DELETE ON data
FOR EACH ROW EXECUTE PROCEDURE backup_data_func();

Вот и все, теперь у нас есть автоматическое резервное копирование вставленных вручную данных!