CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; определяет функцию, которую запросы вызывают так же, как встроенную. Тело пишется между метками $$, на обычном SQL или на PL/pgSQL, процедурном языке PostgreSQL. CREATE PROCEDURE (PostgreSQL 11+) определяет подпрограмму, которую запускают через CALL.
with_tax(50) берёт ставку по умолчанию и возвращает 60; with_tax(50, 0.1) возвращает 55. Аргументы можно передавать и по имени: with_tax(50, rate => 0.1).
Логика на PL/pgSQL
Берите LANGUAGE plpgsql, когда функции нужны переменные, IF, циклы или обработка ошибок. Переменные объявляются в секции DECLARE, а код пишется между BEGIN и END:
Посылка весом 4 кг стоит базовые 5; экспресс-посылка весом 20 кг стоит (5 + 10 * 0.5) * 2 = 20. Внутри PL/pgSQL RAISE NOTICE 'value: %', cost; выводит сообщение, а RAISE EXCEPTION прерывает выполнение с ошибкой.
Если логику можно выразить обычным SQL, выбирайте LANGUAGE sql: простую функцию на SQL планировщик может встроить прямо в вызывающий запрос.
Вернуть набор строк
RETURNS TABLE (...) создаёт функцию, которая возвращает строки. Вызывайте её в FROM, как таблицу:
orders_of('ana') возвращает заказы 1 и 3. RETURNS SETOF orders возвращает целые строки таблицы orders вместо перечисленных столбцов.
Начиная с PostgreSQL 14, у функции на SQL может быть тело в стандартном синтаксисе SQL вместо строки. PostgreSQL хранит его уже разобранным и запоминает используемые таблицы и столбцы, поэтому их нельзя удалить, пока функция существует:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
Хранимая процедура и CALL
У процедуры нет типа результата, её запускают через CALL. Она может менять данные, а если CALL выполняется не внутри явного блока транзакции, может делать COMMIT по ходу работы. Так большую задачу обрабатывают порциями.
На счёте 1 остаётся 70, на счёте 2 становится 80. Перевод 200 нарушил бы ограничение CHECK на счёте 1, и ошибка откатила бы оба обновления.
| Функция | Процедура | |
|---|---|---|
| Как запускать | SELECT f(...), в любом месте запроса | только CALL p(...) |
| Возвращает | значение, строку или набор строк | ничего (или параметры OUT/INOUT) |
COMMIT / ROLLBACK внутри | нет | да, в PL/pgSQL, если вызов не внутри блока транзакции |
| Появилась | всегда была | PostgreSQL 11 |
Категория изменчивости: IMMUTABLE, STABLE, VOLATILE
Метка изменчивости сообщает планировщику, что он может предполагать о функции. По умолчанию это VOLATILE.
IMMUTABLE: результат зависит только от аргументов и никогда не меняется (with_tax). Обязательна для функций в индексном выражении.STABLE: результат может меняться между командами, но не внутри одной, например у функции, которая читает таблицы (orders_of).VOLATILE: всё остальное, например функции сrandom()или меняющие данные. PostgreSQL вызывает такую функцию заново для каждой строки.
Неверная метка даёт неверные результаты: функция IMMUTABLE, которая читает таблицу, может вернуть устаревшие значения из индекса или закешированного плана.
Заменить, перегрузить и удалить функцию
CREATE OR REPLACE FUNCTION меняет тело существующей функции, но не тип результата; для этого функцию сначала удаляют. PostgreSQL различает функции по имени и типам аргументов, поэтому f(int) и f(text) это две разные функции. Чтобы удалить одну из двух функций с одинаковым именем, укажите типы аргументов:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
В главе о PL/pgSQL описаны циклы, курсоры, исключения и динамический SQL через EXECUTE. Чтобы функция запускалась автоматически при изменении строк, привяжите её к триггеру.
Часто задаваемые вопросы
Чем функция отличается от процедуры в PostgreSQL?
Функция возвращает значение и вызывается внутри запроса (SELECT f(x)). Процедура, появившаяся в PostgreSQL 11, запускается отдельно через CALL и может выполнять COMMIT или ROLLBACK в своём теле; функция всегда работает внутри транзакции вызывающего.
Что означает $$ в функции PostgreSQL?
$$ это dollar quoting: метки начала и конца строкового литерала, благодаря которым тело функции может содержать одинарные кавычки без экранирования. Можно добавить и тег: $body$ ... $body$.
Как посмотреть код функции PostgreSQL?
В psql \df выводит список функций, а \sf name печатает определение функции. В SQL запрос SELECT pg_get_functiondef('name'::regproc); возвращает полную команду CREATE OR REPLACE FUNCTION.
Может ли функция PostgreSQL вернуть несколько строк?
Да. Объявите её как RETURNS TABLE (col type, ...) или RETURNS SETOF type и вызывайте в FROM, как таблицу: SELECT * FROM f(1);.