Структура функции

Статья основана на втором видео из 30 тем курса SQL 2.0 — PL/pgSQL в PostgreSQL от Аристова Евгения, который является логическим продолжением курса SQL c 0. Ссылки на видео на платформах RUTUBE и VK видео.

В данной статье подробно разбирается структура функции в PostgreSQL, её параметры и бест практис по базовому синтаксису.

В прошлой статье мы разобрали серверное программирование в PostgreSQL. Назначение и основные особенности.

Презентация и исходники доступны по ссылке.

Определение функции

Функции, написанные на PL/pgSQL, определяются на сервере командами CREATE FUNCTION. Такая команда обычно выглядит, например, так:

CREATE FUNCTION somefunc(integer, text) RETURNS integer                      AS 'тело функции'
LANGUAGE plpgsql;

Синтаксис функции

https://www.postgresql.org/docs/current/sql-createfunction.html

Пример

Результат:

Параметры функции:

• IMMUTABLE показывает, что функция не может модифицировать базу данных и всегда возвращает один и тот же результат при определённых значениях аргументов. Она не обращается к базе данных и не использует информацию, не переданную ей явно в списке аргументов.
• STABLE показывает, что функция не может модифицировать базу данных и в рамках одного сканирования таблицы она всегда возвращает один и тот же результат для определённых значений аргументов, но этот результат может быть разным в разных операторах SQL.
• VOLATILE показывает, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать. Важно, что любая функция с побочными эффектами должна быть классифицирована как изменчивая, даже если её результат вполне предсказуем.

• CALLED ON INPUT (default) показывает, что функция будет вызвана как обычно, если среди её аргументов оказываются значения NULL. В этом случае ответственность за проверку значений и соответствующую их обработку ложится на разработчика функции.
• RETURNS ON INPUT показывает, что функция всегда возвращает NULL, получив в одном из аргументов. Такая функция не будет вызываться с аргументами NULL, вместо этого автоматически будет полагаться результат.
• STRICT аналогичен указанию RETURNS ON INPUT, обычно пишут полностью, чтобы не путаться.

• SECURITY INVOKER — default (безопасность вызывающего) показывает, что функция будет выполняться с правами пользователя, вызвавшего её.
• SECURITY DEFINER (безопасность определившего) определяет, что функция выполняется с правами пользователя, владеющего ей.

• PARALLEL UNSAFE (default) означает, что эту функцию нельзя выполнять в параллельном режиме и присутствие такой функции в операторе SQL приводит к выбору последовательного плана выполнения.
• PARALLEL RESTRICTED означает, что функцию можно выполнять в параллельном режиме, но только в ведущем процессе группы. PARALLEL SAFE показывает, что функция безопасна для выполнения в параллельном режиме без ограничений.
• SAFE — можно параллелить используя максимальное количество max_parallel_workers.

• LANGUAGE — кроме базового SQL, есть ещё другие популярные варианты:
• PL/pgSQL (Chapter 41),
• PL/Tcl (Chapter 42)
• PL/Perl (Chapter 43)
• PL/Python (Chapter 44)
• Более подробно на следующей лекции

Общие замечания

• Ключевые слова не чувствительны к регистру символов. Как и в обычных SQL- командах, идентификаторы неявно преобразуются к нижнему регистру, если они не взяты в двойные кавычки.
• Комментарии в PL/pgSQL коде работают так же, как и в обычном SQL. Двойное тире (—) начинает комментарий, который завершается в конце строки. Блочный комментарий начинается с /* и завершается */. Блочные комментарии могут быть вложенными.
• Используйте читабельный синтаксис — не забывайте про отступы.

Ограничения

1. Ограничения на размер исходного кода

• Размер текста функции: Код функции хранится в системном каталоге pg_proc в столбце prosrc типа text. Теоретически, размер поля text может достигать 1 ГБ. Однако на практике такой огромный скрипт будет абсолютно непрактичным.

2. Практические ограничения и проблемы Хотя технически можно создать гигантский скрипт, на пути встают практические ограничения:

• Производительность компиляции: Перед первым выполнением код PL/pgSQL компилируется в дерево выражений (expression tree). Чем больше скрипт, тем дольше компиляция и тем больше память она consumes.
• Потребление памяти: Большие функции, особенно те, что используют много переменных, сильнее нагружают сервер. Каждая сессия, выполняющая такую функцию, будет хранить ее скомпилированное представление в своей памяти.
• Сложность отладки и поддержки: Скрипт размером в несколько мегабайт (десятки тысяч строк) будет кошмаром для понимания, отладки и изменения. Это противоречит принципам хорошего стиля программирования.
• Ограничение на глубину стека: Очень большие функции с глубокой вложенностью блоков и операторов могут превысить лимит глубины стека (max_stack_depth), что приведет к ошибке.

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

1. Разделение на несколько функций: Вынесите логические блоки кода в отдельные, более мелкие функции. Это улучшит читаемость, упростит тестирование и позволит повторно использовать код.
2. Динамический SQL: Для очень больших операций, которые генерируются кодом, иногда лучше строить и выполнять динамический SQL, чтобы не хранить его как статический текст.
3. Внешние скрипты: Возможно есть смысл хранить исходный код функций в файлах системы контроля версий (Git).

В следующей теме курса мы разберём особенности и варианты использования языков SQL, PL/pgSQL, PL/Python.