Вызов функций из SQL-запросов

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

1. Вызывать функцию из одного языка программирования (например, Python или JavaScript).
2. Передавать результат в SQL-запрос.

Это лишний шаг! В PostgreSQL функции можно встраивать прямо в SQL-запрос, сокращая объём кода, ускоряя операции и уменьшая количество вызовов с сервера. Это особенно полезно для:

• Автоматизации вычислений.
• Валидации данных перед вставкой.
• Модификации существующих данных.

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

Начнём с основ и разберём, как использовать функции в обычном запросе SELECT. Пусть у нас есть таблица students с информацией о студентах. Мы хотим написать функцию, которая возвращает текущий возраст студента на основе даты его рождения.

Шаг 1: написание функции

Создадим функцию calculate_age, которая будет принимать дату рождения и возвращать возраст:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Шаг 2: использование функции в запросе SELECT

Теперь мы можем вызывать эту функцию для каждой записи из таблицы:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

Что происходит?

• Для каждой строки из таблицы students функция calculate_age вычисляет возраст.
• Возвращённое значение отображается в столбце age.

Пример результата:

Как видите, ничего сложного, а результат выглядит красиво и профессионально.

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

Функции также полезны при вставке данных. Например, представим, что у нас есть таблица logs, где записываются действия пользователей. Мы хотим вставлять лог-сообщение, используя функцию для автоматической генерации текста.

Шаг 1: создание функции

Напишем функцию generate_log_message, которая принимает имя пользователя и действие, а возвращает текст сообщения:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

Шаг 2: использование функции в INSERT

Теперь вставим сообщение в таблицу logs, вызывая функцию при добавлении строки:

INSERT INTO logs (message)
VALUES (generate_log_message('Отто', 'вход на сайт'));

Результат:

Функция делает всё за нас: заботится о форматировании текста и добавляет временную метку. Это отличный пример, как функции могут автоматизировать рутинные действия.

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

Функции могут быть использованы и для модификации данных в таблице. Пусть у нас есть таблица students, и мы хотим обновить название группы, используя функцию для их перевода на новый курс.

Шаг 1: создание функции

Напишем функцию promote_student, которая принимает старую группу (например, 101) и возвращает новую (например, 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Шаг 2: использование функции в UPDATE

Обновим у всех студентов их группу:

UPDATE students
SET group_name = promote_student(group_name);

Результат:

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

Вызов функций в условиях WHERE

Функции могут быть использованы в условиях фильтрации. Давайте расширим наш пример с возрастом студентов.

Шаг 1: фильтрация по возрасту

Используем уже созданную функцию calculate_age для выбора студентов старше 20 лет:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Результат:

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

Совмещение с агрегатными функциями

Давайте немного усложним задачу. Нам нужно посчитать общее количество студентов младше 22 лет. Функции прекрасно сочетаются с агрегатными функциями, такими как COUNT().

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

Что происходит?

• Функция calculate_age используется в фильтрации.
• COUNT(*) считает количество строк, соответствующих условию.

Реальные примеры применения

Автоматизация валидации данных. Допустим, вы хотите проверить, что возраст всех студентов в таблице находится в типичном диапазоне (например, от 18 до 30 лет). Напишите функцию для проверки и используйте её в условии WHERE.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Оптимизация вставки данных. Представьте, что вы работаете в интернет-магазине. Вместо того чтобы вычислять общую стоимость заказа на клиентской стороне, напишите функцию, которая рассчитает её прямо при добавлении данных в таблицу orders.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Типичные ошибки при вызове функций

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

Отсутствие необходимых прав. Если вы не являетесь владельцем функции или таблицы, PostgreSQL может запретить вызов. Убедитесь, что у вас есть права на выполнение функции.

Несоответствие типов. При передаче аргументов в функцию следите за типами данных. Например, если функция ожидает DATE, а вы передаёте строку, то получите ошибку. Используйте явное преобразование:

SELECT calculate_age('2000-01-01'::DATE);

Ошибки синтаксиса внутри функций. Если функция возвращает ошибку, это может привести к сбою всего запроса. Тщательно тестируйте функции перед использованием.