Что такое функции, процедуры и триггеры
На этой странице
Функция (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
BEFORE | AFTER | |
|---|---|---|
Может изменить 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.