Funzioni in PostgreSQL: casi d'uso pratici
Le funzioni in Postgres sono tanto semplici quanto potenti: permettono di incapsulare logiche complesse, riutilizzare codice e migliorare la manutenzione del database. In questo articolo esploreremo alcuni casi d’uso pratici per illustrare come sfruttare al meglio le funzioni in PostgreSQL.
Al contrario delle stored procedure, le funzioni in PostgreSQL possono restituire un valore e possono essere utilizzate all’interno di query SQL. Questo le rende particolarmente utili per operazioni che richiedono calcoli o trasformazioni di dati. Infatti si dice che le funzioni possono ritornare valori scalari (come numeri o stringhe) o set di righe (simili a tabelle temporanee), mentre le procedure non restituiscono valori e sono utilizzate principalmente per eseguire operazioni di modifica dei dati.
Caso 1: Selezione di dati con condizioni complesse
Supponiamo di avere una tabella ordini con i campi id, cliente_id, data_ordine e importo. Vogliamo creare una funzione che restituisca tutti gli ordini di un cliente specifico effettuati in un intervallo di date. Per questo, usiamo l’istruzione CREATE FUNCTION, seguita dalla definizione della funzione, ossia il nome e i parametri in ingresso e in uscita con la relativa tipologia di dato che ci si aspetta:
CREATE OR REPLACE FUNCTION get_ordini_cliente(cliente_id INT, data_inizio DATE, data_fine DATE)
RETURNS TABLE(id INT, data_ordine DATE, importo NUMERIC) AS $$
BEGIN
RETURN QUERY
SELECT id, data_ordine, importo
FROM ordini
WHERE cliente_id = cliente_id
AND data_ordine BETWEEN data_inizio AND data_fine;
END;
$$ LANGUAGE plpgsql;
In questo caso, andiamo a restituire una tabella con le colonne id, data_ordine e importo. La clausola RETURN QUERY viene utilizzata per eseguire una query SQL e restituire i risultati come output della funzione.
Questa funzione può essere chiamata in una query SQL come segue:
SELECT * FROM get_ordini_cliente(1, '2023-01-01', '2023-12-31');
L’aspetto interessante è che la funzione restituisce una tabella, permettendo di utilizzare i risultati direttamente in altre query. Non a caso, l’istruzione SELECT * FROM viene utilizzata per richiamare la funzione e ottenere tutti gli ordini del cliente con ID 1 effettuati nel 2023. Dal momento che la funzione restituisce una tabella, possiamo trattare i risultati come se fossero una normale tabella SQL e quindi decidere anche di applicare ulteriori filtri od ordinamenti.
Caso 2: Calcolo di valori aggregati
Consideriamo una tabella vendite con i campi id, prodotto_id, quantita e prezzo_unitario. Vogliamo creare una funzione che calcoli il totale delle vendite per un prodotto specifico. Ecco come possiamo farlo:
CREATE OR REPLACE FUNCTION calcola_totale_vendite(prodotto_id INT)
RETURNS NUMERIC AS $$
DECLARE
totale NUMERIC;
BEGIN
SELECT SUM(quantita * prezzo_unitario) INTO totale
FROM vendite
WHERE prodotto_id = prodotto_id;
RETURN totale;
END;
$$ LANGUAGE plpgsql;
In questo caso, la funzione calcola_totale_vendite accetta un parametro prodotto_id e restituisce il totale delle vendite per quel prodotto. La variabile totale viene dichiarata all’interno del blocco DECLARE, e il risultato della query viene assegnato a questa variabile utilizzando l’istruzione SELECT ... INTO. Infine, la funzione restituisce il valore calcolato con l’istruzione RETURN.
Per richiamare questa funzione, possiamo utilizzare la seguente query:
SELECT calcola_totale_vendite(1);
Questa query restituirà il totale delle vendite per il prodotto con ID 1.
Caso 3: Validazione dei dati
Supponiamo di avere una tabella utenti con i campi id, email e password. Vogliamo creare una funzione che verifichi se un’email è già presente nel database prima di inserire un nuovo utente. Ecco come possiamo implementare questa logica:
CREATE OR REPLACE FUNCTION email_esiste(email TEXT)
RETURNS BOOLEAN AS $$
DECLARE
esiste BOOLEAN;
BEGIN
SELECT COUNT(*) > 0 INTO esiste
FROM utenti
WHERE email = email;
RETURN esiste;
END;
$$ LANGUAGE plpgsql;
In questo caso, la funzione email_esiste accetta un parametro email e restituisce un valore booleano che indica se l’email esiste già nella tabella utenti. La variabile esiste viene dichiarata all’interno del blocco DECLARE, e il risultato della query viene assegnato a questa variabile utilizzando l’istruzione SELECT ... INTO. Infine, la funzione restituisce il valore booleano con l’istruzione RETURN.
Anche in questo caso, per utilizzare questa funzione, possiamo eseguire la seguente query:
SELECT email_esiste('pippo@pluto.it');
Questa query restituirà TRUE se l’email esiste già nella tabella utenti, altrimenti restituirà FALSE.
Conclusioni
Le funzioni in PostgreSQL sono strumenti potenti che permettono di incapsulare logiche complesse, migliorare la riusabilità del codice e facilitare la manutenzione del database. Nei casi d’uso presentati, abbiamo visto come le funzioni possono essere utilizzate per selezionare dati con condizioni complesse, calcolare valori aggregati e validare dati prima dell’inserimento.
Quando ha senso utilizzarle, al posto di funzioni applicative? In breve, possiamo dire che le funzioni in PostgreSQL sono particolarmente utili quando la logica di business è strettamente legata ai dati e deve essere eseguita frequentemente. Incapsulare questa logica nel database può migliorare le prestazioni, ridurre la latenza e garantire che le regole di business siano applicate in modo coerente. Tuttavia, è importante bilanciare l’uso delle funzioni con la complessità del sistema, poiché un uso eccessivo può rendere il database difficile da gestire e mantenere.
