Creazione di procedure multi-step: validazione dati, inserimento e logging

Nelle situazioni di business reali non basta fare una sola operazione, ma spesso serve una catena di azioni: tipo, quando arriva un ordine — controllare i dati del cliente, salvare l’ordine, scrivere un log per l’audit. Una procedura multi-step ti permette di unire questi passaggi in una logica unica e garantire l’integrità grazie alle transazioni: se qualcosa va storto in qualsiasi step — tutto viene rollbackato.

Con le nuove versioni di PostgreSQL, soprattutto da quando ci sono le procedure (CREATE PROCEDURE) e una gestione delle transazioni più avanzata, è importante capire la differenza tra funzione e procedura PL/pgSQL, e anche come usare bene i savepoint (SAVEPOINT), i rollback e i blocchi di gestione errori.

Basi della struttura di una procedura multi-step

Una tipica procedura di business è fatta di questi step:

1. Validazione dati — controllare gli argomenti in input, la presenza del cliente/prodotto ecc.
2. Inserimento dati — aggiungere (o aggiornare) effettivamente la/le riga(-he).
3. Logging o audit — scrivere info sull’operazione riuscita o fallita.

Ogni step si può fare dentro una singola transazione (in modo atomico), oppure, se il processo è “lungo” o serve gestire errori a pezzi, — creare dei savepoint (SAVEPOINT) e usare blocchi di gestione eccezioni per rollback locali.

Esempio: aggiunta di un ordine con controllo di integrità

Immagina questa situazione — ci sono tre tabelle:

• customers — clienti
• orders — ordini
• order_log — log degli ordini

Prepariamo lo schema:

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

Creazione di una procedura multi-step: FUNZIONE o PROCEDURA?

Importante!

• Se ti serve il pieno controllo sulle transazioni (savepoint, COMMIT/ROLLBACK espliciti) — usa CREATE PROCEDURE.
• Se la procedura è logicamente atomica ("tutto o niente") e viene chiamata da altre query SQL — usa una funzione.

Versione come funzione (logica atomica):

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. Validazione cliente
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Cliente con ID % non esiste', p_customer_id;
    END IF;

    -- 2. Inserimento ordine
    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, 'Ordine creato con successo.');

    RAISE NOTICE 'Ordine % per cliente % aggiunto con successo', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Nota: le funzioni in PostgreSQL vengono sempre eseguite dentro una transazione esterna. Non puoi usare dentro una funzione la gestione delle transazioni (COMMIT, ROLLBACK, SAVEPOINT). Il commit o il rollback avviene fuori.

Versione con gestione errori e logging degli errori:

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
        -- Validazione cliente
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Cliente con ID % non esiste', p_customer_id;
        END IF;

        -- Inserimento ordine
        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, 'Ordine creato con successo.');

        RAISE NOTICE 'Ordine % per cliente % aggiunto con successo', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Errore: %', SQLERRM));
            RAISE; -- Rollback di tutta la transazione della funzione
    END;
END;
$$ LANGUAGE plpgsql;

Blocco BEGIN ... EXCEPTION ... END: In PL/pgSQL, dentro funzioni e procedure, questo blocco crea un savepoint virtuale. Tutte le modifiche dentro il blocco vengono rollbackate se c’è un errore.

Commit parziali e gestione step-by-step: perché servono le procedure

Se ti serve fare commit a step (cioè fissare parzialmente) — usa le PROCEDURE!

In PostgreSQL dalla versione 11 puoi scrivere procedure (CREATE PROCEDURE) che possono gestire transazioni e savepoint lato server. Solo nelle PROCEDURE (non nelle funzioni!) puoi fare COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT in modo esplicito. Ma: il comando ROLLBACK TO SAVEPOINT in una procedura PL/pgSQL è vietato — usa i gestori di eccezioni.

Esempio di procedura con gestione step-by-step e gestione errori

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
    -- Primo blocco: validazione cliente
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Cliente con ID % non esiste', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Errore (validate): %', SQLERRM));
            RETURN;
    END;

    -- Secondo blocco: inserimento ordine
    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('Errore (order): %', SQLERRM));
            RETURN;
    END;

    -- Terzo blocco: logging dell’operazione riuscita
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Ordine creato con successo.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Qui non importa, anche se il logging non va
            RAISE NOTICE 'Impossibile scrivere il log per l’ordine %', v_order_id;
    END;

    RAISE NOTICE 'Ordine % per cliente % aggiunto con successo (procedura)', v_order_id, p_customer_id;
END;
$$;

Chiamata della procedura:

CALL add_order_step_by_step(1, 150.50);

Best practice per lavorare con transazioni e procedure

• Usa le funzioni per operazioni di business atomiche — quando vuoi il principio "tutto o niente".
• Per commit step-by-step o rollback isolati degli step — usa le procedure e chiamale fuori da una transazione esplicita (modalità autocommit).
• Per “rollback parziale” usa i blocchi BEGIN ... EXCEPTION ... END — dentro questi PL/pgSQL crea da solo un savepoint e fa rollback delle modifiche del blocco se c’è errore.
• Logga gli errori — è il modo migliore per capire perché qualcosa non si è caricato o non ha funzionato.
• Non usare ROLLBACK TO SAVEPOINT nelle procedure PL/pgSQL — ti darà errore di sintassi (limite di PostgreSQL 17+).

Testing: scenario di successo e scenario di errore

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

-- Chiamiamo la funzione (deve andare a buon fine)
SELECT add_order(1, 300.00);

-- Chiamiamo la funzione con un cliente che non esiste (ci sarà errore)
SELECT add_order(999, 100.00);

-- Vediamo il log
SELECT * FROM order_log;