Erstellen von mehrstufigen Prozeduren: Datenprüfung, Einfügen und Logging

In echten Business-Szenarien reicht es nicht, nur eine Operation auszuführen – meistens braucht man eine Kette von Aktionen: Zum Beispiel bei einer Bestellung – Kundendaten prüfen, Bestellung speichern, einen Audit-Log schreiben. Eine mehrstufige Prozedur verbindet diese Schritte zu einer einzigen Logik und garantiert Konsistenz dank Transaktionen: Wenn irgendwo was schiefgeht, wird alles zurückgerollt.

Mit den neuen PostgreSQL-Versionen, vor allem seit es eigene Prozeduren (CREATE PROCEDURE) und mehr Möglichkeiten für Transaktionen gibt, ist es wichtig, den Unterschied zwischen Funktion und Prozedur in PL/pgSQL zu verstehen – und wie man Savepoints (SAVEPOINT), Rollbacks und Error-Handling richtig nutzt.

Grundstruktur einer mehrstufigen Prozedur

Ein typischer Business-Prozess besteht aus diesen Schritten:

1. Datenprüfung – Validierung der Eingabewerte, ob Kunde/Produkt existiert usw.
2. Daten einfügen – das eigentliche Hinzufügen (oder Updaten) von Datensätzen.
3. Logging oder Audit – Infos über erfolgreiche oder fehlgeschlagene Operationen speichern.

Jeden Schritt kann man in einer einzigen Transaktion (atomar) machen, oder – wenn der Prozess "lang" ist oder Fehler einzeln behandelt werden sollen – Savepoints (SAVEPOINT) setzen und Exception-Blöcke für lokalen Rollback nutzen.

Beispiel: Bestellung mit Integritätskontrolle hinzufügen

Schauen wir uns folgende Situation an – es gibt drei Tabellen:

• customers – Kunden
• orders – Bestellungen
• order_log – Bestell-Log

Das Schema sieht so aus:

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

Mehrstufige Prozedur erstellen: FUNKTION oder PROZEDUR?

Wichtig!

• Wenn du volle Kontrolle über Transaktionen brauchst (Savepoints, explizites COMMIT/ROLLBACK) – nutze CREATE PROCEDURE.
• Wenn die Prozedur logisch atomar ist ("alles oder nichts") und von anderen SQL-Queries aufgerufen wird – nimm eine Funktion.

Variante als Funktion (atomare Logik):

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. Kundenprüfung
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Kunde mit ID % existiert nicht', p_customer_id;
    END IF;

    -- 2. Bestellung einfügen
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Logging
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Bestellung erfolgreich erstellt.');

    RAISE NOTICE 'Bestellung % für Kunde % erfolgreich hinzugefügt', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Besonderheit: Funktionen in PostgreSQL laufen immer in einer äußeren Transaktion. Du kannst darin kein Transaktions-Handling machen (COMMIT, ROLLBACK, SAVEPOINT). Rollback oder Commit passiert von außen.

Variante mit Fehlerbehandlung und Error-Logging:

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
        -- Kundenprüfung
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Kunde mit ID % existiert nicht', p_customer_id;
        END IF;

        -- Bestellung einfügen
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Logging
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Bestellung erfolgreich erstellt.');

        RAISE NOTICE 'Bestellung % für Kunde % erfolgreich hinzugefügt', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Fehler: %', SQLERRM));
            RAISE; -- Rollback der ganzen Funktionstransaktion
    END;
END;
$$ LANGUAGE plpgsql;

Block BEGIN ... EXCEPTION ... END: In PL/pgSQL erzeugt dieser Block einen virtuellen Savepoint. Alles im Block wird zurückgerollt, wenn ein Fehler auftritt.

Teil-Commits und Schritt-für-Schritt-Handling: Wozu Prozeduren?

Wenn du Schritt-für-Schritt-Commits brauchst (also wirklich Teil-Commits) – nimm PROZEDUREN!

Seit PostgreSQL 11 kann man eigene Prozeduren (CREATE PROCEDURE) schreiben, die Transaktionen und Savepoints auf dem Server steuern. Nur in PROZEDUREN (nicht in Funktionen!) darfst du explizit COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT machen. Aber: ROLLBACK TO SAVEPOINT ist in PL/pgSQL-Prozeduren verboten – nutze Exception-Handler.

Beispiel für eine Prozedur mit Schritt-für-Schritt-Handling und Fehlerbehandlung

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
    -- Erster Block: Kundenprüfung
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Kunde mit ID % existiert nicht', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Fehler (validate): %', SQLERRM));
            RETURN;
    END;

    -- Zweiter Block: Bestellung einfügen
    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('Fehler (order): %', SQLERRM));
            RETURN;
    END;

    -- Dritter Block: Logging der erfolgreichen Operation
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Bestellung erfolgreich erstellt.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Hier egal, auch wenn Logging nicht klappt
            RAISE NOTICE 'Konnte Log für Bestellung % nicht schreiben', v_order_id;
    END;

    RAISE NOTICE 'Bestellung % für Kunde % erfolgreich hinzugefügt (Prozedur)', v_order_id, p_customer_id;
END;
$$;

Prozedur aufrufen:

CALL add_order_step_by_step(1, 150.50);

Best Practices für Transaktionen und Prozeduren

• Nutze Funktionen für atomare Business-Operationen – wenn du das "alles oder nichts"-Prinzip brauchst.
• Für Schritt-für-Schritt-Commit oder isoliertes Rollback von Schritten – nutze Prozeduren und ruf sie außerhalb einer expliziten Transaktion auf (Autocommit-Modus).
• Für "Teil-Rollback" nutze BEGIN ... EXCEPTION ... END-Blöcke – darin macht PL/pgSQL automatisch einen Savepoint und rollt bei Fehlern alles im Block zurück.
• Logge Fehler – das ist der beste Weg, um zu checken, warum was nicht geladen oder ausgeführt wurde.
• Nutze kein ROLLBACK TO SAVEPOINT in PL/pgSQL-Prozeduren – das gibt einen Syntaxfehler (PostgreSQL 17+ Einschränkung).

Testen: Erfolgs- und Fehlerfall

-- Kunde hinzufügen
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Funktion aufrufen (sollte klappen)
SELECT add_order(1, 300.00);

-- Funktion mit nicht existierendem Kunden aufrufen (gibt Fehler)
SELECT add_order(999, 100.00);

-- Log-Tabellen checken
SELECT * FROM order_log;