Виклик функцій із 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);

Синтаксичні помилки всередині функцій. Якщо функція повертає помилку, це може призвести до збою всього запиту. Ретельно тестуй функції перед використанням.