Что такое хранимые функции в PostgreSQL?

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

Хранимая функция (stored function) — это объект базы данных, содержащий именованный блок кода, который компилируется и сохраняется на сервере. Функции можно вызывать из SQL-запросов, триггеров или других функций.

Основные характеристики

• Хранятся в системном каталоге базы данных
• Выполняются на стороне сервера, снижая сетевой трафик
• Могут быть написаны на PL/pgSQL, SQL, PL/Python, PL/V8 и других языках
• Обязательно возвращают значение (в отличие от процедур PROCEDURE)
• Поддерживают перегрузку — несколько функций с одним именем, но разными параметрами

Синтаксис создания

CREATE OR REPLACE FUNCTION имя_функции(параметры)
RETURNS тип_возврата
LANGUAGE plpgsql
AS $$
DECLARE
  -- объявление переменных
BEGIN
  -- тело функции
  RETURN значение;
END;
$$;

Типы возвращаемых значений

Скалярные значения — функция возвращает одно значение:

CREATE OR REPLACE FUNCTION get_user_age(user_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
  v_age INT;
BEGIN
  SELECT age INTO v_age FROM users WHERE id = user_id;
  RETURN v_age;
END;
$$;

RETURNS TABLE — функция возвращает набор строк:

CREATE OR REPLACE FUNCTION get_active_users(min_age INT)
RETURNS TABLE(id INT, name TEXT, email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT u.id, u.name, u.email
    FROM users u
    WHERE u.age >= min_age AND u.is_active = TRUE;
END;
$$;

-- Вызов как обычная таблица
SELECT * FROM get_active_users(18);

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

PostgreSQL различает три категории, влияющие на оптимизацию:

• VOLATILE (по умолчанию) — может возвращать разные результаты при одних входных данных, не кешируется
• STABLE — в рамках одной транзакции возвращает одинаковый результат для одних параметров
• IMMUTABLE — результат зависит только от аргументов, можно использовать в индексах

CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT amount * 0.2;
$$;

Функции на чистом SQL

Для простых операций без ветвлений можно использовать язык sql:

CREATE OR REPLACE FUNCTION full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT first_name || ' ' || last_name;
$$;

Отличие от процедур (PROCEDURE)

Управление функциями

-- Просмотр исходного кода функции
\df+ имя_функции

-- Удаление функции
DROP FUNCTION IF EXISTS get_user_age(INT);