Функции, процедуры, триггеры

SQL — это не только запросы: внутри базы можно писать и программы. Функции, процедуры и триггеры переносят логику в саму базу. Здесь PostgreSQL и MySQL отличаются сильнее всего — переключите курс и посмотрите оба варианта.

Функция

Возвращает значение и используется внутри запроса.

CREATE FUNCTION mark(score int) RETURNS int
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN score >= 86 THEN 5 WHEN score >= 71 THEN 4 WHEN score >= 56 THEN 3 ELSE 2 END
$$;

SELECT student_id, score, mark(score) FROM grades;

$$ … $$ — тело функции (кавычки внутри удваивать не нужно). Для сложной логики используют LANGUAGE plpgsql: переменные, IF, циклы.

Процедура

Не обязана возвращать значение, выполняет несколько действий и вызывается через CALL.

CREATE PROCEDURE raise_salary(dept int, pct numeric)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE employees SET salary = salary * (1 + pct / 100) WHERE department_id = dept;
END $$;

CALL raise_salary(1, 10);

Триггер

Срабатывает сам, когда в таблице происходит событие (INSERT, UPDATE, DELETE). Пример: при продаже лекарства уменьшать остаток на складе.

CREATE FUNCTION decrease_stock() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE dori SET qoldiq = qoldiq - NEW.miqdor WHERE dori_id = NEW.dori_id;
  RETURN NEW;
END $$;

CREATE TRIGGER sotuv_after_insert
AFTER INSERT ON sotuv
FOR EACH ROW EXECUTE FUNCTION decrease_stock();

В PostgreSQL триггер состоит из двух частей: сначала функция, потом триггер, который её вызывает.

NEW — добавляемая (или обновлённая) строка, OLD — удаляемая (или прежняя) строка.

Когда это использовать?

Полезно: поддерживать целостность (остаток никогда не уходит в минус), вести историю изменений, выполнять тяжёлые расчёты рядом с данными.

Осторожно: логика становится «невидимой» — программист пишет INSERT, но не знает, что ещё делает триггер. Документируйте триггеры и используйте их умеренно.

Упражнение

В свободном режиме загрузите готовую базу аптеки, добавьте в таблицу dori столбец qoldiq int DEFAULT 100 (ALTER TABLE), создайте триггер выше и добавьте продажу — остаток должен уменьшиться.

Запомните

• Функция возвращает значение, процедура выполняет действия (CALL), триггер реагирует на событие.
• PostgreSQL: $$ … $$ и plpgsql; MySQL: DELIMITER и BEGIN … END.
• Триггер — мощный, но невидимый инструмент: осторожнее.