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

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

С выходом новых версий 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 'Customer with ID % does not exist', 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, 'Order successfully created.');

    RAISE NOTICE 'Order % for customer % added successfully', 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 'Customer with ID % does not exist', 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, 'Order successfully created.');

        RAISE NOTICE 'Order % for customer % added successfully', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error: %', 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 'Customer with ID % does not exist', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error (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('Error (order): %', SQLERRM));
            RETURN;
    END;

    -- Третий блок: логирование успешной операции
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Order successfully created.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Здесь не важно, даже если логирование не сработало
            RAISE NOTICE 'Failed to write log for order %', v_order_id;
    END;

    RAISE NOTICE 'Order % for customer % added successfully (procedure)', 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;

2

Задача

SQL SELF, 53 уровень, 2 лекция

Недоступна

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

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

Открыть