Хранимые функции PostgreSQL

Хранимая функция PostgreSQL — это объект базы данных, представляющий собой именованный набор операторов SQL и procedural-языковых конструкций (например, PL/pgSQL, PL/Python, PL/Tcl), который хранится на сервере и может быть вызван пользователем или приложением для выполнения определённой задачи. Хранимые функции в PostgreSQL являются ключевым инструментом для инкапсуляции бизнес-логики, повышения производительности за счёт снижения сетевого трафика и обеспечения целостности данных. В отличие от представлений (views), функции могут принимать параметры, возвращать значения различных типов (включая таблицы) и выполнять операции, изменяющие состояние базы данных.

История и развитие

Поддержка хранимых функций в PostgreSQL появилась в версии 6.0 (1997 год) с внедрением языка PL/pgSQL (Procedural Language/PostgreSQL). Изначально он был создан как альтернатива сложным и небезопасным пользовательским функциям на C, позволяя писать процедурную логику непосредственно в SQL-среде. В последующих версиях язык был расширен: в PostgreSQL 7.3 (2002) добавлена поддержка вложенных транзакций, в PostgreSQL 8.0 (2005) — возможность возвращать наборы строк (SETOF), а в PostgreSQL 9.0 (2010) — поддержка оконных функций и CTE (Common Table Expressions). Начиная с PostgreSQL 11 (2018), хранимые процедуры (PROCEDURE) были выделены в отдельный тип объектов, отличающийся от функций возможностью управления транзакциями (например, выполнение COMMIT или ROLLBACK внутри тела). В настоящее время PostgreSQL поддерживает несколько процедурных языков: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, а также возможность подключения сторонних языков через расширения, такие как PL/Java, PL/R и PL/v8.

Классификация и виды

Хранимые функции в PostgreSQL классифицируются по нескольким признакам.

По возвращаемому значению

• Функции, возвращающие скалярное значение — возвращают одно значение определённого типа (INTEGER, TEXT, NUMERIC и т.д.). Пример: функция, вычисляющая возраст человека по дате рождения.
• Функции, возвращающие набор строк (SETOF) — возвращают таблицу, состоящую из одной или нескольких строк. Используются для генерации отчётов или фильтрации данных.
• Функции, возвращающие таблицу (TABLE) — современный синтаксис, позволяющий явно указывать столбцы возвращаемой таблицы. Введён в PostgreSQL 8.4 (2009).
• Функции, возвращающие void — не возвращают никакого значения, используются для выполнения действий (например, обновления записей) без возврата результата.

По языку реализации

• PL/pgSQL — основной процедурный язык, встроенный в PostgreSQL. Поддерживает переменные, циклы, условные операторы, обработку исключений.
• SQL — чисто SQL-функции, состоящие из одного или нескольких SQL-запросов. Отличаются высокой производительностью, но ограничены в логике.
• C — функции, написанные на языке C, компилируемые в разделяемые библиотеки. Обеспечивают максимальную производительность, но требуют глубоких знаний и перекомпиляции при изменении.
• PL/Python, PL/Perl, PL/Tcl — функции, написанные на интерпретируемых языках, позволяющие использовать их стандартные библиотеки. Требуют установки соответствующих расширений.

По категории волатильности

Каждая функция в PostgreSQL должна быть помечена одной из трёх категорий волатильности, влияющей на оптимизацию запросов:

• VOLATILE — функция может возвращать разные значения при одинаковых аргументах (например, random(), now()). Вызывается каждый раз при выполнении запроса.
• STABLE — функция гарантирует одинаковый результат для одних и тех же аргументов в пределах одного оператора (например, current_timestamp). Может быть оптимизирована при повторных вызовах.
• IMMUTABLE — функция всегда возвращает одинаковый результат для одних и тех же аргументов (например, математические функции). Может быть заменена константой на этапе планирования запроса.

Устройство и синтаксис

Хранимая функция создаётся с помощью команды CREATE FUNCTION. Основные элементы синтаксиса:

``sql CREATE [OR REPLACE] FUNCTION имя_функции (параметры) RETURNS тип_возвращаемого_значения [LANGUAGE язык_реализации] [IMMUTABLE | STABLE | VOLATILE] [AS $$ -- тело функции $$]; ``

Параметры могут быть входными (IN), выходными (OUT) или смешанными (INOUT). В теле функции на PL/pgSQL используются блоки BEGIN ... END, переменные объявляются в секции DECLARE, а для возврата значения применяется RETURN. Пример простой функции, вычисляющей площадь круга:

``sql CREATE OR REPLACE FUNCTION circle_area(radius NUMERIC) RETURNS NUMERIC LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN pi() radius radius; END; $$; ``

Для функций, возвращающих набор строк, используется RETURN NEXT или RETURN QUERY. Пример функции, возвращающей список сотрудников старше определённого возраста:

``sql CREATE OR REPLACE FUNCTION employees_over_age(min_age INTEGER) RETURNS TABLE(id INTEGER, name TEXT, age INTEGER) LANGUAGE plpgsql STABLE AS $$ BEGIN RETURN QUERY SELECT id, name, age FROM employees WHERE age > min_age; END; $$; ``

Применение и значение

Хранимые функции в PostgreSQL широко используются в различных сценариях:

• Инкапсуляция бизнес-логики — сложные вычисления, проверки и преобразования данных выполняются на сервере, что снижает нагрузку на клиентское приложение и упрощает его разработку.
• Повышение производительности — выполнение логики на сервере уменьшает количество сетевых запросов и позволяет использовать оптимизации планировщика PostgreSQL.
• Обеспечение целостности данных — функции могут использоваться в триггерах для автоматического выполнения действий при вставке, обновлении или удалении записей.
• Генерация отчётов — функции, возвращающие таблицы, позволяют создавать сложные отчёты с агрегацией, фильтрацией и сортировкой.
• Управление доступом — через функции можно реализовать ограничение доступа к данным, скрывая внутреннюю структуру таблиц.

Примеры использования

Функция для расчёта налоговой скидки

``sql CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC) RETURNS NUMERIC LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN price * (1 - discount_rate / 100); END; $$; ``

Функция для проверки корректности email

``sql CREATE OR REPLACE FUNCTION is_valid_email(email TEXT) RETURNS BOOLEAN LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'; END; $$; ``

Функция для генерации случайного пароля

``sql CREATE OR REPLACE FUNCTION generate_password(length INTEGER) RETURNS TEXT LANGUAGE plpgsql VOLATILE AS $$ DECLARE chars TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; result TEXT := ''; BEGIN FOR i IN 1..length LOOP result := result || substr(chars, floor(random() * length(chars) + 1)::INTEGER, 1); END LOOP; RETURN result; END; $$; ``

Критика и ограничения

Несмотря на широкое применение, хранимые функции в PostgreSQL имеют ряд недостатков:

• Сложность отладки — отладка PL/pgSQL-функций менее удобна по сравнению с кодом на языках общего назначения (например, Python или Java). Отсутствие встроенного отладчика в стандартной поставке усложняет поиск ошибок.
• Ограниченная переносимость — синтаксис и возможности PL/pgSQL специфичны для PostgreSQL, что затрудняет миграцию на другие СУБД (например, Oracle или MySQL).
• Производительность при сложных вычислениях — для вычислительно-ёмких задач (например, обработка больших массивов данных) функции на C или на внешних языках могут быть значительно быстрее.
• Риск ошибок безопасности — неправильно написанные функции могут быть уязвимы для SQL-инъекций, особенно если они используют динамический SQL (например, EXECUTE с конкатенацией строк).

Интересные факты

• PostgreSQL поддерживает перегрузку функций: можно создать несколько функций с одинаковым именем, но разными типами параметров.
• Функции могут быть объявлены как SECURITY DEFINER, что позволяет им выполняться с правами создателя, а не вызывающего пользователя.
• Начиная с PostgreSQL 14, в PL/pgSQL добавлена поддержка многострочных строковых литералов с использованием $tag$, что упрощает написание сложных функций.
• В PostgreSQL 15 появилась возможность использовать RETURN в хранимых процедурах, что ранее было доступно только для функций.

Источники

• PostgreSQL Documentation: Chapter 37. Procedural Languages (PL/pgSQL, PL/Tcl, PL/Perl, PL/Python).
• PostgreSQL Documentation: Chapter 38. Functions and Operators.
• PostgreSQL Documentation: Chapter 39. Triggers.
• «PostgreSQL. Основы языка SQL» — Е. П. Моргунов, 2020.
• «PostgreSQL 14. Изнутри» — Д. В. Рогов, 2022.