Création de procédures multi-étapes : vérification des données, insertion et journalisation

Dans les scénarios business réels, il ne suffit pas de faire une seule opération, il faut souvent enchaîner plusieurs actions : par exemple, quand une commande arrive — vérifier les infos du client, enregistrer la commande, écrire un log pour l’audit. Une procédure multi-étapes permet de regrouper tout ça dans une seule logique et de garantir l’intégrité grâce aux transactions : si un truc foire à n’importe quelle étape — tout est annulé.

Avec les nouvelles versions de PostgreSQL, surtout depuis l’apparition des procédures (CREATE PROCEDURE) et l’extension de la gestion des transactions, c’est important de piger la différence entre une fonction et une procédure PL/pgSQL, et aussi — comment bien bosser avec les savepoints (SAVEPOINT), les rollbacks, les blocs d’erreur.

Bases de la structure d’une procédure multi-étapes

Une procédure business typique se compose d’étapes :

1. Vérification des données — validation des arguments d’entrée, existence du client/produit, etc.
2. Insertion des données — ajout (ou mise à jour) réel de la/les ligne(s).
3. Journalisation ou audit — écrire une info sur l’opération réussie ou échouée.

Chaque étape peut être faite dans une seule transaction (atomique), ou, si le process est "long" ou demande une gestion d’erreur par morceaux, — tu peux créer des savepoints (SAVEPOINT) et utiliser des blocs d’exceptions pour faire un rollback local.

Exemple : ajout d’une commande avec contrôle d’intégrité

Regarde la situation suivante — on a trois tables :

• customers — clients
• orders — commandes
• order_log — journal des commandes

Préparons le schéma :

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

Création d’une procédure multi-étapes : FONCTION ou PROCÉDURE ?

Important !

• Si tu veux un contrôle total sur les transactions (savepoints, COMMIT/ROLLBACK explicites) — utilise CREATE PROCEDURE.
• Si la procédure est logiquement atomique ("tout ou rien") et appelée dans d’autres requêtes SQL — utilise une fonction.

Version sous forme de fonction (logique atomique) :

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. Vérification du client
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Le client avec l’ID % n’existe pas', p_customer_id;
    END IF;

    -- 2. Insertion de la commande
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Journalisation
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Commande créée avec succès.');

    RAISE NOTICE 'Commande % pour le client % ajoutée avec succès', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Particularité : les fonctions dans PostgreSQL sont toujours exécutées dans une seule transaction externe. Tu ne peux pas faire de gestion transactionnelle (COMMIT, ROLLBACK, SAVEPOINT) à l’intérieur d’une fonction. Le commit ou rollback se fait à l’extérieur.

Version avec gestion des erreurs et log des erreurs :

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
        -- Vérification du client
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Le client avec l’ID % n’existe pas', p_customer_id;
        END IF;

        -- Insertion de la commande
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Journalisation
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Commande créée avec succès.');

        RAISE NOTICE 'Commande % pour le client % ajoutée avec succès', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Erreur : %', SQLERRM));
            RAISE; -- Rollback de toute la transaction de la fonction
    END;
END;
$$ LANGUAGE plpgsql;

Bloc BEGIN ... EXCEPTION ... END : En PL/pgSQL, dans les fonctions et procédures, ce bloc crée un savepoint virtuel. Toutes les modifs dans le bloc sont annulées si une erreur arrive.

Commits partiels et gestion étape par étape : pourquoi utiliser les procédures

Si tu veux faire des commits par étape (genre vraiment partiel) — utilise les PROCÉDURES !

Depuis PostgreSQL 11, tu peux écrire des procédures (CREATE PROCEDURE) qui gèrent les transactions et les savepoints côté serveur. Uniquement dans les PROCÉDURES (pas dans les fonctions !) tu peux faire explicitement COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT. Mais : la commande ROLLBACK TO SAVEPOINT dans une procédure PL/pgSQL est interdite — utilise les gestionnaires d’exceptions à la place.

Exemple de procédure avec gestion étape par étape et gestion des erreurs

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
    -- Premier bloc : vérification du client
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Le client avec l’ID % n’existe pas', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Erreur (validation) : %', SQLERRM));
            RETURN;
    END;

    -- Deuxième bloc : insertion de la commande
    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('Erreur (commande) : %', SQLERRM));
            RETURN;
    END;

    -- Troisième bloc : journalisation de l’opération réussie
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Commande créée avec succès.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Ici, c’est pas grave même si le log ne marche pas
            RAISE NOTICE 'Impossible d’écrire le log pour la commande %', v_order_id;
    END;

    RAISE NOTICE 'Commande % pour le client % ajoutée avec succès (procédure)', v_order_id, p_customer_id;
END;
$$;

Appel de la procédure :

CALL add_order_step_by_step(1, 150.50);

Bonnes pratiques pour bosser avec les transactions et les procédures

• Utilise des fonctions pour les opérations business atomiques — quand tu veux le principe "tout ou rien".
• Pour un commit étape par étape ou un rollback isolé d’étapes — utilise des procédures et appelle-les hors d’une transaction explicite (mode autocommit).
• Pour un "rollback partiel" utilise les blocs BEGIN ... EXCEPTION ... END — dedans, PL/pgSQL crée un savepoint et annule les modifs du bloc si erreur.
• Loggue les erreurs — c’est le meilleur moyen de piger pourquoi un truc n’a pas été chargé ou n’a pas marché.
• N’utilise pas ROLLBACK TO SAVEPOINT dans les procédures PL/pgSQL — ça va faire une erreur de syntaxe (limite PostgreSQL 17+).

Tests : scénario réussi et scénario d’erreur

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

-- Appelons la fonction (ça doit marcher)
SELECT add_order(1, 300.00);

-- Appelons la fonction avec un client qui n’existe pas (ça va planter)
SELECT add_order(999, 100.00);

-- Vérifions le journal des logs
SELECT * FROM order_log;