Tworzenie procedur wieloetapowych: sprawdzanie danych, insert i logowanie

W prawdziwych scenariuszach biznesowych nie chodzi tylko o wykonanie jednej operacji, ale o zbudowanie łańcucha działań: na przykład przy składaniu zamówienia — sprawdzenie danych klienta, zapisanie zamówienia, zapis do logów dla audytu. Procedura wieloetapowa pozwala połączyć te kroki w jedną logikę i zagwarantować spójność dzięki transakcjom: jeśli na którymkolwiek etapie coś pójdzie nie tak — zmiany są cofane.

Wraz z pojawieniem się nowych wersji PostgreSQL, szczególnie po wprowadzeniu osobnych procedur (CREATE PROCEDURE) i rozszerzeniu pracy z transakcjami, ważne jest, żeby rozumieć różnicę między funkcją a procedurą PL/pgSQL, a także — poprawną pracę z savepointami (SAVEPOINT), rollbackami, blokami obsługi błędów.

Podstawy struktury procedury wieloetapowej

Typowa procedura biznesowa składa się z etapów:

1. Sprawdzanie danych — walidacja argumentów wejściowych, istnienia klienta/produktu itp.
2. Insert danych — faktyczne dodanie (lub update) rekordu(-ów).
3. Logowanie lub audyt — zapis informacji o udanej lub nieudanej operacji.

Każdy etap można wykonać w ramach jednej transakcji (atomowo), albo, jeśli proces jest "długi" lub wymaga obsługi błędów po kawałkach, — tworzyć savepointy (SAVEPOINT) i używać bloków obsługi wyjątków do lokalnego rollbacka.

Przykład: dodanie zamówienia z kontrolą spójności

Rozważmy taką sytuację — mamy trzy tabele:

• customers — klienci
• orders — zamówienia
• order_log — log zamówień

Przygotujmy schemat:

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()
);

Tworzenie procedury wieloetapowej: FUNKCJA czy PROCEDURA?

Ważne!

• Jeśli potrzebujesz pełnej kontroli nad transakcjami (savepointy, jawne COMMIT/ROLLBACK) — używaj CREATE PROCEDURE.
• Jeśli procedura jest logicznie atomowa ("wszystko albo nic") i wywoływana z innych zapytań SQL — używaj funkcji.

Wersja jako funkcja (logika atomowa):

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. Sprawdzanie klienta
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Klient o ID % nie istnieje', p_customer_id;
    END IF;

    -- 2. Insert zamówienia
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Logowanie
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Zamówienie zostało utworzone pomyślnie.');

    RAISE NOTICE 'Zamówienie % dla klienta % dodane pomyślnie', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Cechy szczególne: funkcje w PostgreSQL zawsze wykonują się w ramach jednej zewnętrznej transakcji. Nie można używać w funkcji zarządzania transakcjami (COMMIT, ROLLBACK, SAVEPOINT). Rollback lub commit następuje na zewnątrz.

Wersja z obsługą błędów i logowaniem błędów:

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
        -- Sprawdzanie klienta
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Klient o ID % nie istnieje', p_customer_id;
        END IF;

        -- Insert zamówienia
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Logowanie
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Zamówienie zostało utworzone pomyślnie.');

        RAISE NOTICE 'Zamówienie % dla klienta % dodane pomyślnie', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Błąd: %', SQLERRM));
            RAISE; -- Rollback całej transakcji funkcji
    END;
END;
$$ LANGUAGE plpgsql;

Blok BEGIN ... EXCEPTION ... END: W PL/pgSQL, wewnątrz funkcji i procedur, ten blok tworzy wirtualny savepoint. Wszystkie zmiany w bloku są cofane, jeśli pojawi się błąd.

Częściowe commity i przetwarzanie krok po kroku: po co procedury

Jeśli potrzebujesz commitowania etapami (prawdziwy częściowy commit) — używaj PROCEDUR!

W PostgreSQL od wersji 11 pojawiła się możliwość pisania osobnych procedur (CREATE PROCEDURE), które mogą zarządzać transakcjami i savepointami po stronie serwera. Tylko w PROCEDURACH (nie w funkcjach!) można jawnie wykonać COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT. Ale: komenda ROLLBACK TO SAVEPOINT w procedurze PL/pgSQL jest zabroniona — używaj obsługi wyjątków.

Przykład procedury z przetwarzaniem krok po kroku i obsługą błędów

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
    -- Pierwszy blok: sprawdzanie klienta
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Klient o ID % nie istnieje', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Błąd (walidacja): %', SQLERRM));
            RETURN;
    END;

    -- Drugi blok: insert zamówienia
    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('Błąd (zamówienie): %', SQLERRM));
            RETURN;
    END;

    -- Trzeci blok: logowanie udanej operacji
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Zamówienie zostało utworzone pomyślnie.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Tutaj nie ma znaczenia, nawet jeśli logowanie się nie udało
            RAISE NOTICE 'Nie udało się zapisać loga dla zamówienia %', v_order_id;
    END;

    RAISE NOTICE 'Zamówienie % dla klienta % dodane pomyślnie (procedura)', v_order_id, p_customer_id;
END;
$$;

Wywołanie procedury:

CALL add_order_step_by_step(1, 150.50);

Dobre praktyki przy pracy z transakcjami i procedurami

• Używaj funkcji do atomowych operacji biznesowych — kiedy potrzebujesz zasady "wszystko albo nic".
• Do commitowania krok po kroku lub izolowanego rollbacka etapów — używaj procedur i wywołuj je poza jawną transakcją (tryb autocommit).
• Do "częściowego rollbacka" używaj bloków BEGIN ... EXCEPTION ... END — w nich PL/pgSQL sam tworzy savepoint i cofa zmiany bloku przy błędzie.
• Loguj błędy — to najlepszy sposób, żeby zrozumieć, czemu coś się nie załadowało albo nie zadziałało.
• Nie używaj ROLLBACK TO SAVEPOINT w procedurach PL/pgSQL — to wywoła błąd składni (ograniczenie PostgreSQL 17+).

Testowanie: scenariusz udany i błędny

-- Dodajmy klienta
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Wywołajmy funkcję (powinno przejść pomyślnie)
SELECT add_order(1, 300.00);

-- Wywołajmy funkcję z nieistniejącym klientem (będzie błąd)
SELECT add_order(999, 100.00);

-- Sprawdźmy logi
SELECT * FROM order_log;