Створення процедур з кількома етапами: перевірка даних, вставка і логування

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

З виходом нових версій PostgreSQL, особливо після появи окремих процедур (CREATE PROCEDURE) і розширення роботи з транзакціями, важливо розуміти різницю між функцією і процедурою PL/pgSQL, а також — правильну роботу з точками збереження (SAVEPOINT), відкатами, блоками помилок.

Основи структури багатокрокової процедури

Типова бізнес-процедура складається з етапів:

1. Перевірка даних — валідація вхідних аргументів, наявності клієнта/товару і т.д.
2. Вставка даних — фактичне додавання (або оновлення) запису(-ів).
3. Логування або аудит — запис інформації про успішну або неуспішну операцію.

Кожен етап можна виконувати всередині однієї транзакції (атомарно), або, якщо процес "довгий" чи потребує обробки помилок по частинах, — створювати точки збереження (SAVEPOINT) і використовувати блоки обробки виключень для локального відкату.

Приклад: додавання замовлення з контролем цілісності

Розглянемо таку ситуацію — є три таблиці:

• customers — клієнти
• orders — замовлення
• order_log — журнал замовлень

Підготуємо схему:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

Створення багатокрокової процедури: ФУНКЦІЯ чи ПРОЦЕДУРА?

Важливо!

• Якщо тобі потрібен повний контроль над транзакціями (точки збереження, явні COMMIT/ROLLBACK) — використовуй CREATE PROCEDURE.
• Якщо процедура логічно атомарна ("все або нічого") і викликається з інших SQL-запитів — використовуй функцію.

Версія у вигляді функції (атомарна логіка):

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. Перевірка клієнта
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Клієнта з ID % не існує', p_customer_id;
    END IF;

    -- 2. Вставка замовлення
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Логування
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Замовлення успішно створено.');

    RAISE NOTICE 'Замовлення % для клієнта % додано успішно', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Особливість: функції в PostgreSQL завжди виконуються в рамках однієї зовнішньої транзакції. Не можна використовувати всередині функції транзакційне управління (COMMIT, ROLLBACK, SAVEPOINT). Відкат або коміт відбувається ззовні.

Версія з обробкою помилок і логуванням помилок:

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- Перевірка клієнта
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Клієнта з ID % не існує', p_customer_id;
        END IF;

        -- Вставка замовлення
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Логування
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Замовлення успішно створено.');

        RAISE NOTICE 'Замовлення % для клієнта % додано успішно', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Помилка: %', SQLERRM));
            RAISE; -- Відкат всієї транзакції функції
    END;
END;
$$ LANGUAGE plpgsql;

Блок BEGIN ... EXCEPTION ... END: У PL/pgSQL, всередині функцій і процедур, цей блок створює віртуальний savepoint. Всі зміни всередині блоку відкочуються, якщо виникає помилка.

Часткові коміти і покрокова обробка: навіщо потрібні процедури

Якщо потрібен поетапний коміт (реально часткова фіксація) — використовуй ПРОЦЕДУРИ!

У PostgreSQL з версії 11 з'явилася можливість писати окремі процедури (CREATE PROCEDURE), які можуть керувати транзакціями і сейвпоінтами на стороні сервера. Тільки в ПРОЦЕДУРАХ (не у функціях!) дозволено явно виконувати COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT. Але: команда ROLLBACK TO SAVEPOINT у процедурі PL/pgSQL заборонена — використовуй обробники виключень.

Приклад процедури з покроковою обробкою і обробкою помилок

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- Перший блок: перевірка клієнта
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Клієнта з ID % не існує', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Помилка (validate): %', SQLERRM));
            RETURN;
    END;

    -- Другий блок: вставка замовлення
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Помилка (order): %', SQLERRM));
            RETURN;
    END;

    -- Третій блок: логування успішної операції
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Замовлення успішно створено.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Тут не важливо, навіть якщо логування не спрацювало
            RAISE NOTICE 'Не вдалося записати лог для замовлення %', v_order_id;
    END;

    RAISE NOTICE 'Замовлення % для клієнта % додано успішно (процедура)', v_order_id, p_customer_id;
END;
$$;

Виклик процедури:

CALL add_order_step_by_step(1, 150.50);

Гарні практики при роботі з транзакціями і процедурами

• Використовуй функції для атомарних бізнес-операцій — коли потрібен принцип "все або нічого".
• Для покрокового коміту або ізольованого відкату етапів — використовуй процедури і викликай їх поза явною транзакцією (режим autocommit).
• Для "часткового відкату" використовуй блоки BEGIN ... EXCEPTION ... END — всередині них PL/pgSQL сам створює savepoint і відкочує зміни блоку при помилці.
• Логуй помилки — найкращий спосіб зрозуміти, чому щось не завантажилось або не спрацювало.
• Не використовуй ROLLBACK TO SAVEPOINT у процедурах PL/pgSQL — це викличе синтаксичну помилку (обмеження PostgreSQL 17+).

Тестування: успішний і помилковий сценарій

-- Додаємо клієнта
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Викликаємо функцію (має пройти успішно)
SELECT add_order(1, 300.00);

-- Викликаємо функцію з неіснуючим клієнтом (буде помилка)
SELECT add_order(999, 100.00);

-- Перевіряємо журнал логів
SELECT * FROM order_log;