До сих пор мы гоняли запросы по одному. Но часто логику удобнее держать рядом с данными - внутри самой базы. В этом уроке разберём серверное программирование в postgresql: чем функция отличается от процедуры, как принимать и возвращать значения, как вернуть целую таблицу, зачем нужна категория волатильности (IMMUTABLE/STABLE/VOLATILE) и какие языки кроме PL/pgSQL вообще есть. Это фундамент под триггеры, которые пойдут дальше.
Как это работает
Сервер выполняет вашу процедурную логику прямо внутри своего процесса. Это значит меньше сетевых раундов: вместо десяти запросов из приложения вы один раз вызываете функцию, и весь цикл крутится на стороне сервера. Расплата - логику сложнее тестировать и версионировать, чем код приложения, так что в базу выносят то, что неотделимо от данных.
PostgreSQL различает две сущности. Функция - это выражение: её вызывают внутри запроса (SELECT, WHERE, в списке колонок), она обязана вернуть значение и не управляет транзакцией. Процедура появилась в PostgreSQL 11, её вызывают отдельной командой CALL, она может ничего не возвращать и, главное, умеет управлять транзакцией внутри себя - делать COMMIT и ROLLBACK по ходу выполнения. Функцию внутри транзакции прервать на COMMIT нельзя, потому что она часть выражения.
Языков несколько. Чистый SQL-функция - тело это просто один или несколько SQL-операторов, планировщик часто умеет встроить (inline) такую функцию в запрос, и она работает быстро. PL/pgSQL - полноценный процедурный язык с переменными, IF, циклами, обработкой исключений; он идёт в коробке и покрывает 95 процентов задач. Есть и другие PL: PL/Python, PL/Perl, PL/Tcl - их подключают расширением, когда нужна логика, неудобная на SQL.
Параметры бывают трёх режимов. IN - входной (режим по умолчанию). OUT - выходной, через него функция отдаёт значение наружу. INOUT - и туда, и обратно. Несколько OUT-параметров автоматически складываются в строку-композит, так что функция может вернуть сразу несколько значений без создания типа вручную.
Категория волатильности - это обещание планировщику о поведении функции. VOLATILE (по умолчанию) - может вернуть разное при одинаковых аргументах и/или менять данные, оптимизатор её не трогает. STABLE - в пределах одного запроса при тех же аргументах результат не меняется (например, читает таблицы, но не пишет). IMMUTABLE - всегда один результат на одни аргументы, не зависит ни от чего внешнего; такую функцию можно вычислить заранее и использовать в индексе по выражению. Соврёте планировщику в категории - получите тихо неверные результаты, это классические грабли.
SQL и примеры
Простая SQL-функция. Вернём название аэропорта по коду из демобазы Авиаперевозки:
Код: Выделить всё
CREATE FUNCTION airport_name(p_code char(3))
RETURNS text
LANGUAGE sql
STABLE
AS $$
SELECT airport_name FROM airports WHERE airport_code = p_code;
$$;
SELECT airport_name('SVO');
Функция на PL/pgSQL с логикой и OUT-параметрами. Посчитаем число рейсов и среднюю стоимость билета по аэропорту вылета:
Код: Выделить всё
CREATE FUNCTION dep_stats(
p_code char(3),
OUT flights_cnt bigint,
OUT avg_amount numeric
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
SELECT count(*), avg(tf.amount)
INTO flights_cnt, avg_amount
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
WHERE f.departure_airport = p_code;
END;
$$;
SELECT * FROM dep_stats('LED');
Возврат таблицы через RETURNS TABLE. Вернём топ рейсов по выручке для аэропорта:
Код: Выделить всё
CREATE FUNCTION top_flights(p_code char(3), p_limit int DEFAULT 5)
RETURNS TABLE(flight_no char(6), revenue numeric)
LANGUAGE sql
STABLE
AS $$
SELECT f.flight_no, sum(tf.amount) AS revenue
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
WHERE f.departure_airport = p_code
GROUP BY f.flight_no
ORDER BY revenue DESC
LIMIT p_limit;
$$;
SELECT * FROM top_flights('SVO', 3);
Процедура с управлением транзакцией. Процедура - единственное место, где можно делать COMMIT внутри тела, поэтому ей удобно бить большую правку на порции:
Код: Выделить всё
CREATE PROCEDURE bump_amounts(p_step numeric)
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE ticket_flights SET amount = amount + p_step
WHERE fare_conditions = 'Economy';
COMMIT;
END;
$$;
CALL bump_amounts(100);
Пометка по версиям: в PostgreSQL 14 процедуры научились возвращать значения через OUT-параметры (раньше только INOUT). В PostgreSQL 16 добавили SQL/JSON конструкторы, а в 17 заметно ускорили сам интерпретатор PL/pgSQL и расширили MERGE (поддержка RETURNING и обновляемых представлений).
Пример на другом PL - PL/Python (нужно расширение, ставит суперпользователь):
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS plpython3u;
CREATE FUNCTION py_upper(s text) RETURNS text
LANGUAGE plpython3u
IMMUTABLE
AS $$
return s.upper()
$$;
SELECT py_upper('svo');
Частые грабли
- Соврать в волатильности. Пометили VOLATILE-функцию как IMMUTABLE - оптимизатор закеширует результат, и вы получите устаревшие данные без единой ошибки в логе.
- Завернуть в одинарные кавычки тело с кавычками внутри. Используйте dollar quoting $$ или $func$ - читается чище и не ломается на апострофах.
- Ждать от функции отдельной транзакции. У функции своего COMMIT нет, она всегда внутри транзакции вызывающего. Нужен COMMIT в теле - это процедура.
- Путать RETURN и RETURN NEXT/RETURN QUERY в set-returning функциях на PL/pgSQL. Для возврата множества строк - RETURN QUERY или RETURN NEXT в цикле.
- Думать, что SECURITY DEFINER безопасен по умолчанию. Он выполняет функцию с правами владельца, и без явного SET search_path открывает дорогу подмене объектов.
- Перегружать (overload) функции с DEFAULT-параметрами так, что вызов становится неоднозначным - PostgreSQL откажется выбирать и выдаст ошибку.
- Подключитесь к демобазе: psql -d demo
- Создайте SQL-функцию city_by_code(char(3)), возвращающую город аэропорта из airports. Пометьте STABLE и проверьте на коде 'SVO'.
- Напишите функцию на PL/pgSQL с RETURNS TABLE, возвращающую номер рейса и число мест из seats для заданной модели самолёта (aircraft_code).
- Создайте процедуру, которая в цикле уменьшает amount в ticket_flights на 1 процент для класса Comfort и делает COMMIT. Вызовите через CALL.
- Сделайте функцию с двумя OUT-параметрами: минимальная и максимальная цена билета по рейсу (flight_id). Вызовите через SELECT * FROM.
- Попробуйте создать функцию RETURNS text без RETURN в теле на PL/pgSQL - посмотрите на ошибку, затем исправьте.
- Через \df+ имя_функции посмотрите категорию волатильности и язык каждой созданной функции.
- Чем функция принципиально отличается от процедуры в плане управления транзакцией?
- Что обещает планировщику пометка IMMUTABLE и почему такую функцию можно использовать в индексе по выражению?
- Когда выбрать RETURNS TABLE, а когда несколько OUT-параметров?
- Зачем нужен dollar quoting и чем он лучше одинарных кавычек?
- В чём опасность SECURITY DEFINER без явного search_path?
- Чем отличается trusted-язык от untrusted (суффикс u) и кто может создавать функции на untrusted PL?
