re:re:actable
Урок 103 минуты чтения

Что такое функции, процедуры и триггеры

На этой странице
Кратко

Функция (CREATE FUNCTION) — именованный код в базе на SQL или PL/pgSQL, который принимает аргументы, возвращает значение или набор строк и вызывается внутри запроса. Процедура (CREATE PROCEDURE, PostgreSQL 11+) вызывается через CALL, ничего не возвращает (кроме OUT-параметров) и может управлять транзакцией (COMMIT внутри). Триггер — функция, которую база автоматически вызывает при INSERT/UPDATE/DELETE (BEFORE/AFTER, FOR EACH ROW/STATEMENT). Типичные применения триггеров: updated_at, аудит изменений, поддержание денормализованных счётчиков, NOTIFY об изменениях. Минусы: «невидимая» логика, которую не видно из кода приложения, сложность тестирования и отладки, дополнительная нагрузка на каждую строку. Бизнес-логику обычно держат в приложении, а в базе — то, что должно выполняться всегда, независимо от клиента.

Вопрос

Что такое функции, процедуры и триггеры в PostgreSQL и когда их использовать?

Функция на SQL

CREATE FUNCTION order_total(p_order_id bigint) RETURNS numeric
LANGUAGE sql STABLE AS $$
  SELECT COALESCE(sum(qty * unit_price), 0) FROM order_items WHERE order_id = p_order_id;
$$;
 
SELECT id, order_total(id) FROM orders WHERE user_id = 1;

Категория волатильности (IMMUTABLE, STABLE, VOLATILE) подсказывает планировщику, можно ли кешировать результат; только IMMUTABLE-функции разрешены в индексах по выражению.

Триггер updated_at

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Триггер аудита

CREATE TABLE audit_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text NOT NULL,
  op text NOT NULL,
  row_id bigint,
  old_data jsonb,
  new_data jsonb,
  changed_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE FUNCTION audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_name, op, row_id, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NULL;   -- для AFTER-триггера возвращаемое значение игнорируется
END;
$$;
 
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit();

BEFORE и AFTER

BEFOREAFTER
Может изменить NEWДаНет
Может отменить операцию для строкиДа (RETURN NULL)Нет (только исключением)
Типичное применениеНормализация, updated_at, валидацияАудит, счётчики, уведомления

Процедура

CREATE PROCEDURE archive_old_orders(batch int)
LANGUAGE plpgsql AS $$
BEGIN
  LOOP
    WITH moved AS (
      DELETE FROM orders WHERE id IN (
        SELECT id FROM orders WHERE created_at < now() - interval '3 years' LIMIT batch
      ) RETURNING *
    )
    INSERT INTO orders_archive SELECT * FROM moved;
    EXIT WHEN NOT FOUND;
    COMMIT;          -- фиксируем каждую пачку
  END LOOP;
END;
$$;
 
CALL archive_old_orders(5000);

Когда логика в базе оправдана

ПодходитНе подходит
updated_at, аудит, инварианты, которые должны соблюдаться всегдаБизнес-процессы с внешними вызовами (email, платёжки)
Счётчики, которые обновляются в той же транзакцииСложные правила, которые часто меняются
Тяжёлые пакетные операции рядом с даннымиЛогика, которую нужно покрыть обычными unit-тестами

Частые ошибки

  • Каскады триггеров, вызывающих друг друга, — трудно отлаживать.
  • Тяжёлый FOR EACH ROW триггер при массовом обновлении миллиона строк.
  • Функция помечена IMMUTABLE, хотя читает таблицы, — неверные результаты из-за кеширования.
  • SECURITY DEFINER без фиксированного search_path.

Что важно помнить

  • Функция — в запросе, процедура — через CALL и с управлением транзакциями.
  • Триггер срабатывает автоматически на изменение данных.
  • В базе — то, что должно выполняться всегда; остальное — в приложении.

Мини-шпаргалка

  • CREATE FUNCTION ... RETURNS ... LANGUAGE sql|plpgsql.
  • CREATE PROCEDURE ..., CALL.
  • CREATE TRIGGER ... BEFORE|AFTER ... FOR EACH ROW EXECUTE FUNCTION f().
  • NEW, OLD, TG_OP, TG_TABLE_NAME.

Документация

PostgreSQL: CREATE FUNCTION, PostgreSQL: Triggers, PostgreSQL: PL/pgSQL.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам