Функции и PL/pgSQL

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

Рецепт на кухне

На кухне ресторана висит карточка рецепта: «соус: взять 200 г томатов, если томаты кислые, добавить сахар, варить 20 минут». Повар не придумывает соус каждый раз. Он берёт карточку, подставляет продукты и получает одинаковый результат. Кто бы ни стоял у плиты.

Функция в базе это такая карточка. Ты уже пользовался встроенными: round, coalesce, jsonb_agg. Сегодня пишем свои. Их вызывает любой запрос и любое приложение, подключённое к базе, а правило живёт в одном месте.

Функция на чистом SQL

Самая простая функция это запрос с параметром. Выручка по одной книге:

CREATE FUNCTION book_revenue(p_book_id integer)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT coalesce(sum(oi.qty * oi.price), 0)
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE oi.book_id = p_book_id
    AND o.status <> 'cancelled';
$$;

Разберём по частям:

• book_revenue(p_book_id integer) это имя и параметр с типом. Приставка p_ не обязательна, но спасает от путаницы: у таблиц есть колонка book_id, а параметр называется иначе.
• RETURNS numeric это тип результата.
• LANGUAGE sql говорит, что тело функции написано на обычном SQL.
• Тело стоит между $$ и $$. Это долларовые кавычки: длинная строка, внутри которой можно спокойно писать 'cancelled' в одинарных кавычках.

Вызываем как любую встроенную функцию:

SELECT title, book_revenue(id) AS revenue FROM books WHERE id IN (1, 2, 6) ORDER BY id;

       title        | revenue
--------------------+---------
 Мастер и Маргарита | 1380.00
 Собачье сердце     |       0
 Мёртвые души       |       0
(3 rows)

«Мёртвые души» были только в отменённом заказе, поэтому выручка 0.

PL/pgSQL: когда нужны условия

На одном SQL трудно написать «если так, то это, иначе то». Для этого у PostgreSQL есть свой язык, PL/pgSQL. Это SQL плюс переменные, IF и циклы.

Магазин решил: книги старше 30 лет со скидкой 10%, старше 100 лет со скидкой 30%.

CREATE FUNCTION discount_price(p_price numeric, p_year integer)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  v_age integer := 2026 - p_year;
BEGIN
  IF p_price IS NULL THEN
    RETURN NULL;
  ELSIF v_age >= 100 THEN
    RETURN round(p_price * 0.7, 2);
  ELSIF v_age >= 30 THEN
    RETURN round(p_price * 0.9, 2);
  ELSE
    RETURN p_price;
  END IF;
END;
$$;

Устройство функции на PL/pgSQL:

• DECLARE объявляет переменные. v_age получает возраст книги, := это присваивание.
• Между BEGIN и END идут команды, каждая заканчивается точкой с запятой.
• IF ... ELSIF ... ELSE ... END IF выбирает ветку, RETURN возвращает результат и завершает функцию.

SELECT title, year, price, discount_price(price, year) AS sale FROM books ORDER BY id;

       title        | year |  price  |  sale
--------------------+------+---------+---------
 Мастер и Маргарита | 1967 |  690.00 |  621.00
 Собачье сердце     | 1987 |  390.00 |  351.00
 Пикник на обочине  | 1972 |  450.00 |  405.00
 Трудно быть богом  | 1964 |  520.00 |  468.00
 Шинель             | 1842 |  250.00 |  175.00
 Мёртвые души       | 1842 |  610.00 |  427.00
 Грокаем алгоритмы  | 2016 | 1290.00 | 1290.00
 Совершенный код    | 1993 | 2490.00 | 2241.00
 Нос                | 1836 |         |
(9 rows)

Гоголю скидка 30%, «Совершенному коду» 10%, «Грокаем алгоритмы» без скидки. У «Носа» нет цены, и первая ветка честно вернула NULL, а не ошибку.