PostgreSQL триггеры, как реагировать на изменние значения в определённой колонке

Частый кейс в работе, это настройка уведомлений или других действий, когда данные изменили оригинальное значение. Например, ваши данные обновляются по крону из стороннего API и вы делаете массовую вставку в БД через update или upsert, что бы сэкономить ресурсы. Но в таком случае вы не можете на стороне вашего приложения отслеживать изменения. На помощь приходит, как всегда сам PostgreSQL. В PostgreSQL есть такие фишки, как триггеры и функции.

Давайте рассмотрим на примере моего проекта. У меня есть таблица leads, содержащая информацию о потенциальных клиентах. Я хочу отслеживать изменения в столбце статус, который показывает, был ли лид автоматически назначен продавцу или нет. Каждый раз, когда колонка статуса обновляется с одного значение на другое, я хочу создавать новую запись в отдельной таблице status_log, которая содержит текущее время и новое значение статуса.

На основе таблицы status_log можно будет отображать историю изменений или отправлять уведомления на почту, телеграм итд.

Для начала нам понадобится функция на предмет изменения в колонке статуса и триггер который её запустит.

Функция

В вашей системе управления БД выполните код который создаст новую функцию log_lead_status_changes. Где условие IF NEW.status <> OLD.status THEN отслеживает произошло ли изменение колонки status:

CREATE OR REPLACE FUNCTION log_lead_status_changes()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status <> OLD.status THEN -- only log changes
    INSERT INTO status_log (lead_id, time, new_status)
      VALUES (NEW.id, now(), NEW.status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Триггер

Теперь осталось создать триггер, который будет вызывать нашу функцию:

CREATE TRIGGER lead_status_change
AFTER UPDATE OF status ON leads
FOR EACH ROW
EXECUTE FUNCTION log_lead_status_changes();

Вот и вся магия. Не забудьте создать вашу таблицу status_log куда будут записываться все изменения. Пример:

CREATE TABLE status_log (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER NOT NULL REFERENCES leads(id),
  time TIMESTAMP NOT NULL DEFAULT now(),
  new_status VARCHAR(255) NOT NULL
);

Теперь вы можете написать в своем бэкенде простую очередь, которая будет проверять таблицу status_log и отправлять уведомления об изменениях. А затем удалить запись из таблицы журнала.