Функции и процедуры внутри PostgreSQL

PostgreSQL умеет хранить не только данные, но и выполняемый код. Функция получает аргументы, возвращает значение или набор строк и может использоваться в SELECT. Процедура вызывается отдельной командой CALL и предназначена для последовательности действий.

Серверный код полезен, когда правило должно быть доступно всем клиентам базы. Но он не обязан заменять код приложения: начинайте с самого простого решения.

Если нужен один запрос, достаточно языка SQL:

Функция принимает номер заказа и возвращает сумму позиций. Её можно вызвать:

PL/pgSQL — процедурный язык PostgreSQL. Он добавляет переменные, условия, циклы и обработку ожидаемых ошибок. Он нужен для нескольких связанных команд, а не просто потому, что функция находится в базе.

Построчный цикл часто можно заменить одной операцией над набором строк. Такая SQL-команда обычно короче и даёт планировщику больше свободы.

Свойство изменчивости (volatility) — обещание функции планировщику:

Функция суммы заказа читает таблицу, поэтому она STABLE, но не IMMUTABLE. Слишком сильная метка — не ускорение, а ложь. Планировщик может заранее вычислить такую функцию и сохранить устаревший результат в подготовленном плане или индексе.

Иногда текст команды строится во время выполнения. Это называется динамическим SQL. Значения передавайте отдельно через USING, а имя таблицы форматируйте как идентификатор через %I:

$1 безопасно получает значение организации. Имя таблицы нельзя передать как обычное значение, поэтому его нужно сначала проверить по разрешённому списку, а затем корректно заключить в кавычки через %I. Склеивать непроверенный ввод с SQL-командой нельзя.

Обычно функция работает с правами вызывающей роли. SECURITY DEFINER меняет это: функция выполняется с правами владельца. Так можно дать приложению узкую операцию без прямого доступа к таблице, но ошибка создаст повышение привилегий.

Безопасная функция такого типа требует:

Иначе злоумышленник может создать объект с подходящим именем в доступной схеме, а привилегированная функция случайно обратится к нему.

Функция участвует в выражении и вызывается через SELECT. Процедура вызывается через CALL. В некоторых контекстах процедура может управлять транзакциями, но на это действуют ограничения.

Не выбирайте процедуру только из-за длины кода. Выбор определяется способом вызова, возвращаемым результатом и необходимостью управления транзакцией.

В лаборатории 16 вы создадите функцию расчёта суммы и узкую функцию SECURITY DEFINER, а затем проверите доступ от имени роли приложения.

Подробнее: CREATE FUNCTION, язык PL/pgSQL.