Взаємодія між функціями та процедурами

Кілька рівнів тому ми вже піднімали питання процедур і функцій у PostgreSQL. Прийшов час розібратися з ними глибше.

Функції та процедури можуть працювати окремо, але найчастіше саме їх взаємодія визначає успіх всієї системи. Головна зручність у тому, що функції можна викликати одна з одної, передаючи дані і навіть отримуючи результат виконання.

Функції vs Процедури: у чому різниця?

Давай згадаємо, чим функції відрізняються від процедур у PostgreSQL:

• Функції (FUNCTION):

  • Повертають значення.
  • Їх можна використовувати в SELECT.
  • Часто застосовуються для обчислень або перетворення даних.

• Процедури (PROCEDURE):

  • Не повертають значення напряму.
  • Використовуються для виконання операцій, таких як вставка, оновлення або видалення даних.
  • Викликаються за допомогою команди CALL.

Передача даних між функціями

Переходимо до практики — почнемо з базового прикладу передачі даних між функцією і процедурою. По суті, передача даних між функціями відбувається через параметри і повернуті значення.

Ось як виглядає виклик функції всередині іншої функції:

CREATE OR REPLACE FUNCTION get_student_name(student_id INT)
RETURNS TEXT AS $$
DECLARE
    student_name TEXT;
BEGIN
    -- Витягуємо ім'я студента за його ID
    SELECT name INTO student_name FROM students WHERE id = student_id;

    -- Повертаємо ім'я
    RETURN student_name;
END;
$$ LANGUAGE plpgsql;

Цю функцію можна викликати з іншої функції:

CREATE OR REPLACE FUNCTION welcome_student(student_id INT)
RETURNS TEXT AS $$
DECLARE
    message TEXT;
BEGIN
    -- Отримуємо ім'я студента за допомогою іншої функції
    message := 'Ласкаво просимо, ' || get_student_name(student_id) || '!';

    -- Повертаємо привітання
    RETURN message;
END;
$$ LANGUAGE plpgsql;

1. Функція get_student_name повертає ім'я студента за його ідентифікатором (student_id).
2. В іншій функції — welcome_student — це ім'я використовується для створення привітального повідомлення.

Примітка: Витяг даних через SELECT INTO зберігає результат запиту у змінній PL/pgSQL.

Приклад виклику процедур з функцій

Тепер давай розглянемо, як викликати процедуру з функції. Припустимо, у нас є процедура, яка фіксує час входу студента в систему:

CREATE OR REPLACE PROCEDURE log_student_entry(student_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO log_entries(student_id, entry_time)
    VALUES (student_id, NOW());
END;
$$;

Тепер викличемо цю процедуру з функції, де вона буде фіксувати вхід і повертати повідомлення:

CREATE OR REPLACE FUNCTION student_login(student_id INT)
RETURNS TEXT AS $$
BEGIN
    -- Викликаємо процедуру для логування
    CALL log_student_entry(student_id);

    -- Повертаємо повідомлення
    RETURN 'Вхід студента успішно залоговано.';
END;
$$ LANGUAGE plpgsql;

Практичні приклади взаємодії

Приклад 1: розрахунок підсумкової суми і логування замовлення

Уяви, що ти працюєш із системою онлайн-замовлень. Для розрахунку підсумкової суми замовлення у тебе є функція:

CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Підсумовуємо всі позиції замовлення
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

Для збереження підсумкової суми замовлення використовується процедура:

CREATE OR REPLACE PROCEDURE log_order_total(order_id INT, total NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO order_totals(order_id, total)
    VALUES (order_id, total);
END;
$$;

Тепер зв'яжемо їх разом:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS TEXT AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Викликаємо функцію для розрахунку підсумкової суми
    total := calculate_order_total(order_id);

    -- Логуємо підсумкову суму через процедуру
    CALL log_order_total(order_id, total);

    RETURN 'Замовлення успішно оброблено.';
END;
$$ LANGUAGE plpgsql;

Приклад 2: отримання максимального рейтингу студента і оновлення профілю

Функція для отримання максимального рейтингу:

CREATE OR REPLACE FUNCTION get_highest_rating(p_student_id INT)
RETURNS INT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- Знаходимо максимальний рейтинг студента
    SELECT MAX(rating) INTO max_rating
    FROM ratings
    WHERE student_id = p_student_id;

    RETURN max_rating;
END;
$$ LANGUAGE plpgsql;

Процедура для оновлення профілю студента:

CREATE OR REPLACE PROCEDURE update_student_profile(student_id INT, max_rating INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE students
    SET highest_rating = max_rating
    WHERE id = student_id;
END;
$$;

Функція для виклику цих операцій:

CREATE OR REPLACE FUNCTION refresh_student_profile(student_id INT)
RETURNS TEXT AS $$
DECLARE
    max_rating INT;
BEGIN
    -- Отримуємо максимальний рейтинг
    max_rating := get_highest_rating(student_id);

    -- Оновлюємо профіль студента
    CALL update_student_profile(student_id, max_rating);

    RETURN 'Профіль успішно оновлено.';
END;
$$ LANGUAGE plpgsql;

Типові помилки при взаємодії

Одна з найпоширеніших помилок — невідповідність типів даних між функцією і процедурою. Наприклад, якщо твоя процедура очікує параметр типу NUMERIC, а ти передаєш INTEGER, PostgreSQL повідомить про невідповідність типів. Завжди перевіряй, що типи даних збігаються.

Ще одна помилка — це циклічний виклик функцій, коли функція A викликає функцію B, а та, у свою чергу, знову викликає A. Це призводить до нескінченного виклику і краху системи.

Практична цінність

Навіщо нам така взаємодія? У реальному житті функції та процедури працюють як "будівельні блоки" складних систем. Вони дозволяють розбити код на незалежні частини, що спрощує відлагодження, повторне використання і тестування. Наприклад:

• На співбесіді тебе можуть попросити написати функцію, яка викликає процедуру для виконання складної операції. Демонстрація практичних навичок взаємодії буде великим плюсом.
• Під час розробки реальних застосунків, таких як інтернет-магазини, системи логування чи CRM, вміння правильно організувати функціональність через взаємодію функцій і процедур значно спрощує код.

Для подальшого вивчення взаємодії функцій і процедур можеш почитати офіційну документацію по PL/pgSQL.