PostgreSQL: функции и триггеры

Сергей Супрунов

Система управления базами данных PostgreSQL обладает очень мощными средствами, делающими возможным перенос части так называемой бизнес-логики приложения со стороны клиента (по отношению к СУБД) на сторону сервера. При этом сокращается, и порой существенно, трафик между клиентом и сервером, упрощается сопровождение разработанного приложения (сервер один, а клиентов может быть много), повышается надежность в плане целостности данных, в ряде случаев становится проще и сам алгоритм работы приложения.

Данная статья посвящена рассмотрению одного из наиболее эффективных средств – триггеров. Все примеры тестировались на PostgreSQL 7.4.2, работающей на системе FreeBSD 5.2.

Для примера разработаем функцию оформления заказа в простом интернет-магазине, отличительной особенностью которой будет максимально возможный перенос всей логики на сторону СУБД.

Итак, пользователь выбрал интересующий его товар и нажал ссылку «Купить». Далее необходимо:

1. Занести в БД информацию о поступлении заказа.

2. Проверить, есть ли необходимое количество товара на складе.

3. Если есть – зарезервировать товар, уменьшив при этом остаток на складе. Если необходимого количества на складе нет, то проверить, не находится ли в резерве нужный товар свыше 5 дней. Если такое имеется и количество товара по просроченным заказам достаточно для исполнения текущего, то просроченные заказы нужно аннулировать, а товар резервировать для нового заказа.

4. При свободном остатке того или иного товара на складе меньше определенного значения сформировать запись в таблице предупреждений.

5. Все «нештатные» ситуации (невозможность зарезервировать требуемое количество, аннулирование неоплаченного заказа) должны отражаться в таблице предупреждений.

Операции выставления счетов, получения оплаты и т. д. для простоты возложим на менеджеров компании. Методы их взаимодействия с базой данных рассматривать в данной статье не будем. Также не будем резервировать товар частично, если необходимого количества не оказалось – пусть этим тоже занимаются менеджеры. Для наглядности алгоритм того, как должно вести себя наше приложение, представлен на рис. 1.

Рисунок 1

Итак, попытаемся решить сформулированные выше задачи без помощи «внешнего» сценария, по одному-единственному запросу к СУБД.

Структура БД для нашей задачи будет следующая:

n  Таблица «Заказы», orders:

n  Идентификатор заказа, order_id, serial.

n  Информация о покупателе, customer, varchar.

n  Идентификатор товара, goods_id, numeric(3).

n  Количество единиц товара, count, numeric(2).

n  Цена товара, по которой делается заказ, price, numeric(7,2).

n  Дата заказа, date, date.

n  Таблица «Товары», goods:

n  Идентификатор товара, goods_id, serial.

n  Описание товара, goods, varchar.

n  Минимальный остаток на складе, rest, numeric(2).

n  Таблица «Склад», warehouse:

n  Идентификатор товара, goods_id, numeric(3).

n  Количество, count, numeric(5).

n  Таблица «Резерв», reserved: