Procedure in PostgreSQL: casi d'uso pratici
Le procedure in PostgreSQL possono automatizzare operazioni ripetitive, gestire transazioni complesse e semplificare la logica lato database. Da PostgreSQL 11 in poi, il comando principale è CREATE PROCEDURE (da non confondere con le funzioni che invece possono restituire valori).
Caso 1: Semplice inserimento dati
Scopo: automatizzare l’inserimento di una riga in una tabella. Supponiamo di avere la tabella clienti con la relativa procedura:
CREATE TABLE clienti (
id SERIAL PRIMARY KEY,
nome VARCHAR(100),
email VARCHAR(100)
);
CREATE PROCEDURE inserisci_cliente(IN nome_cliente VARCHAR, IN email_cliente VARCHAR)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO clienti(nome, email) VALUES (nome_cliente, email_cliente);
END;
$$;
Per richiamare la procedura, è sufficiente usare l’istruzione CALL seguita dal nome della procedura e dai parametri (se presenti):
CALL inserisci_cliente('Mario Rossi', 'mario.rossi@email.com');
Caso 2: Gestione di transazioni con più operazioni
Scopo: eseguire due modifiche sui dati in modo atomico (es. trasferimento di denaro). Immaginando di avere come esempio una tabella chiamata conti, possiamo definire la seguente procedura che prende in input gli ID dei conti mittente e destinatario, oltre all’importo da trasferire e che può essere eseguita come una singola unità di lavoro grazie all’istruzione UPDATE che aggiorna il salgo di entrambi i conti:
CREATE TABLE conti (
id SERIAL PRIMARY KEY,
saldo DECIMAL
);
CREATE PROCEDURE trasferisci_fondi(
IN id_mittente INT,
IN id_destinatario INT,
IN importo DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
-- riduci saldo mittente
UPDATE conti SET saldo = saldo - importo WHERE id = id_mittente;
-- aumenta saldo destinatario
UPDATE conti SET saldo = saldo + importo WHERE id = id_destinatario;
END;
$$;
Come fatto anche prima, la procedura viene eseguita con CALL:
CALL trasferisci_fondi(1, 2, 100.00);
Caso 3: Procedura con controllo e logging avanzato
Scopo: registrare operazioni, validare condizioni e generare log.
Supponiamo una tabella logistica e una tabella operazioni_log per tracciare gli eventi, e di voler anche tracciare eventuali casi di errore: possiamo creare una procedura che prende in ingresso l’ID di una spedizione e il nuovo stato, verifica che lo stato sia valido (in caso contrario, restituisce un’eccezione!), aggiorna la tabella spedizioni e registra l’operazione nella tabella di log.
CREATE TABLE operazioni_log (
id SERIAL PRIMARY KEY,
descrizione TEXT,
operazione TIMESTAMP DEFAULT NOW()
);
CREATE PROCEDURE aggiorna_spedizione(
IN id_spedizione INT,
IN nuovo_stato VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
-- controllo condizione
IF nuovo_stato NOT IN ('in attesa', 'spedito', 'consegnato') THEN
RAISE EXCEPTION 'Stato non valido!';
END IF;
UPDATE spedizioni SET stato = nuovo_stato WHERE id = id_spedizione;
-- log dell’operazione
INSERT INTO operazioni_log(descrizione)
VALUES (concat('Aggiornato stato spedizione ', id_spedizione, ' a ', nuovo_stato));
END;
$$;
Come sempre, per richiamarla possiamo digitare:
CALL aggiorna_spedizione(3, 'spedito');
Questa particolare caso d’uso dimostra come le procedure possano includere logica di controllo ed eccezioni per garantire l’integrità dei dati e tracciare le operazioni eseguite.
Quando ha senso usare una procedura?
- Separare le procedure dalla logica applicativa migliora manutenibilità e sicurezza.
- Batch di operazioni o logiche transazionali.
Ricordiamo che, come nello sviluppo che usa linguaggi di programmazione, documentare sempre chiaramente i parametri e i comportamenti attesi è fondamentale per la manutenzione futura.
