Хранимые функции в PostgreSQL: инкапсуляция бизнес-логики на уровне базы данных

В предыдущем материале мы разбирали представления (views) — инструмент для упрощения сложных запросов. Сегодня сделаем следующий важный шаг и познакомимся с хранимыми функциями. Если представления можно сравнить с виртуальными таблицами, то функции — это полноценные программные модули, которые выполняют вычисления и возвращают результат прямо на стороне сервера баз данных.

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

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

Ключевые особенности:

• Возвращают значение (число, строку, таблицу и т.д.)

• Могут использоваться в операторах SELECT

• Инкапсулируют сложную логику на стороне БД

• Переиспользуются в разных приложениях и запросах

• Повышают безопасность (пользователь получает доступ к функции, но не к исходным таблицам)

Пример 1: Функция для подсчёта сотрудников по должности

Задача

Создать функцию, которая принимает название должности и возвращает количество активных сотрудников, занимающих эту должность в текущий момент.

Код функции

CREATE OR REPLACE FUNCTION get_employee_count_by_position(pos_name VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    employee_count INTEGER;
BEGIN
    SELECT COUNT(DISTINCT e.id) INTO employee_count
    FROM employees e
    JOIN positions_by_date pbd ON e.id = pbd.employee_id
    JOIN positions p ON pbd.position_id = p.id
    WHERE p.position_name = pos_name
      AND pbd.date_start <= CURRENT_DATE
      AND (pbd.date_finish IS NULL OR pbd.date_finish > CURRENT_DATE)
      AND e.is_active = true
      AND p.is_active = true;
    
    RETURN employee_count;
END;
$$ LANGUAGE plpgsql;

Разбор структуры

Логика запроса внутри функции

Запрос объединяет три таблицы:

• employees — данные о сотрудниках

• positions_by_date — связь сотрудников с должностями по периодам

• positions — справочник должностей

Условия фильтрации:

• Должность соответствует переданному названию

• Период работы актуален на текущую дату

• Сотрудник активен

• Должность активна

Вызов функции

-- Сколько менеджеров в компании?
SELECT get_employee_count_by_position('Менеджер');

Результат: 0 (в компании нет активных менеджеров)

-- Сколько бухгалтеров?
SELECT get_employee_count_by_position('Бухгалтер');

Результат: 1 (один активный бухгалтер)

Преимущество: вместо написания сложного JOIN-запроса каждый раз мы просто вызываем функцию с нужным параметром.

Пример 2: Функция, возвращающая таблицу с историей сотрудника

Задача

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

Код функции

CREATE OR REPLACE FUNCTION get_employee_career_history(emp_id INTEGER)
RETURNS TABLE (
    full_name TEXT,
    position_name VARCHAR,
    start_date DATE,
    finish_date DATE,
    duration_days INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        CONCAT(e.last_name, ' ', e.first_name) AS full_name,
        p.position_name,
        pbd.date_start,
        pbd.date_finish,
        COALESCE(pbd.date_finish, CURRENT_DATE) - pbd.date_start AS duration_days
    FROM employees e
    JOIN positions_by_date pbd ON e.id = pbd.employee_id
    JOIN positions p ON pbd.position_id = p.id
    WHERE e.id = emp_id
    ORDER BY pbd.date_start DESC;
END;
$$ LANGUAGE plpgsql;

Ключевые отличия от первой функции

1. RETURNS TABLE — функция возвращает не скалярное значение, а таблицу

2. Явное описание структуры — перечислены все поля с типами

3. RETURN QUERY — специальная конструкция, наполняющая результирующую таблицу данными из запроса

4. Расчёт продолжительности — COALESCE(date_finish, CURRENT_DATE) - date_start даёт количество дней (если дата окончания не указана, используется текущая дата)

Вызов функции

SELECT * FROM get_employee_career_history(1);

Результат:

Что мы видим:

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

• Автоматический расчёт продолжительности в днях

• Для текущей должности указана конечная дата 2099 год (условное обозначение «бессрочно»)

• Сортировка от новых к старым позициям

Преимущества использования хранимых функций

1. Инкапсуляция сложности

Вся логика работы с данными скрыта внутри функции. Приложению не нужно знать структуру таблиц и детали JOIN-объединений.

2. Переиспользование

Одну и ту же функцию можно вызывать из разных частей приложения, из отчётов, из административных скриптов.

3. Согласованность

Любые изменения в логике расчётов вносятся в одном месте — в теле функции. Все вызывающие стороны автоматически получают обновлённое поведение.

4. Производительность

Функции хранятся в скомпилированном виде, план выполнения оптимизируется, сетевой трафик сокращается (передаётся только результат, а не промежуточные данные).

5. Безопасность

Можно дать пользователю право вызывать функцию, но не давать прямого доступа к таблицам.

Где найти созданные функции?

В среде pgAdmin все хранимые функции доступны в навигаторе:

1. Найдите вашу базу данных

2. Раскройте раздел Schemas → public

3. Найдите папку Functions

4. При необходимости обновите список через Refresh

Практические рекомендации

Когда стоит создавать функцию?

✅ Сложная логика, используемая в нескольких местах
✅ Расчёты, требующие временных таблиц или циклов
✅ Бизнес-правила, которые должны применяться единообразно
✅ Операции, которые лучше выполнять на сервере (ближе к данным)

Чего избегать?

❌ Слишком простых операций, которые легко написать в запросе
❌ Функций, выполняющих тяжелые вычисления на больших объёмах данных без индексов
❌ Избыточного количества параметров (больше 3-4)

Дальнейшие шаги

В этом уроке мы рассмотрели два типа функций:

• Скалярные функции — возвращают одно значение (число, строку)

• Табличные функции — возвращают набор записей

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

Заключение

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

Попробуйте самостоятельно:

1. Создать функцию для подсчёта количества сотрудников в подчинении у конкретного менеджера

2. Написать функцию, возвращающую среднюю продолжительность работы в компании по должностям

3. Модифицировать пример с историей, добавив фильтр по периоду

Хотите увидеть процесс создания и отладки хранимых функций в реальном времени — с разбором типичных ошибок, советами по оптимизации и демонстрацией работы в pgAdmin? В нашем видео-руководстве мы:

• Пошагово создаём обе функции из этого урока

• Разбираем каждую строку кода

• Показываем, как отлаживать функции при возникновении ошибок

• Демонстрируем реальные примеры вызова с разными параметрами

• Объясняем, как читать планы выполнения для функций

Статья подготовлена по материалам обучающего видео для вашего удобства.