Differenze tra funzioni e procedure

In molti linguaggi di programmazione tra funzioni e procedure non c'è quasi differenza. In SQL invece sì. In PostgreSQL funzioni e procedure non sono solo due modi diversi di lanciare codice. Sono paradigmi di pensiero diversi.

Una funzione in SQL non può modificare i dati nel database. Deve lavorare solo con i dati passati e restituire un risultato basato su di essi. È fatta per essere usata dentro le query SELECT.

Una procedura in SQL è fatta per modificare il database. Quindi può lavorare con le transazioni (a differenza delle funzioni), scrivere roba nel db. E non può essere usata dentro le query SELECT.

Ecco un confronto veloce:

Differenze in SQL

Nel SQL normale, una funzione è come un'espressione: calcola e restituisce un valore. Una procedura è un comando: fa qualcosa, ma non partecipa nelle espressioni.

Funzione in SQL

SELECT calculate_discount(200);

• Può essere usata in WHERE, ORDER BY, INSERT, UPDATE ecc.
• Deve essere pura: non deve cambiare lo stato del db (se IMMUTABLE/STABLE).

Procedura in SQL

CALL process_order(123);

• Non restituisce risultato.
• Può fare COMMIT, ROLLBACK, chiamare RAISE, lanciare cicli.

Differenze in PL/pgSQL

Le funzioni in PostgreSQL si possono vedere come un gruppo di calcoli. Sono molto flessibili: puoi passare parametri, usare if, cicli, cursori, subquery, restituire righe, scalari, tabelle.

Funzioni in PL/pgSQL

CREATE FUNCTION square(x INT) RETURNS INT AS $$
BEGIN
    RETURN x * x;
END;
$$ LANGUAGE plpgsql;

Caratteristiche:

• RETURNS obbligatorio
• Può usare DECLARE, BEGIN, END, LOOP, IF, CASE
• Non si può fare COMMIT/ROLLBACK
• Si può chiamare in SELECT, UPDATE, CHECK, WHERE, RETURNING

Chiamata:

SELECT square(5);  -- restituirà 25

Procedure in PL/pgSQL

Le procedure sono un meccanismo di gestione delle azioni. Sono perfette quando devi:

• fare tanti step con logica;
• aggiornare e inserire grandi quantità di dati;
• usare gestione delle transazioni: COMMIT, ROLLBACK, SAVEPOINT.

CREATE PROCEDURE log_event(msg TEXT) AS $$
BEGIN
    INSERT INTO logs(message) VALUES (msg);
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Caratteristiche:

• Nessun RETURNS
• Si chiama solo con CALL
• Si può usare COMMIT, ROLLBACK, SAVEPOINT
• Perfetta per batch, migrazioni, ETL

Chiamata:

CALL log_event('Elaborazione completata');

Perché funzioni e procedure sono separate

Perché hanno scopi diversi in SQL:

Il vantaggio chiave della procedura — COMMIT

Le procedure possono gestire le transazioni al loro interno. Cioè, direttamente dentro la procedura puoi fare:

BEGIN;
-- logica
SAVEPOINT point1;
-- tentativo di aggiornamento
ROLLBACK TO point1;
COMMIT;

Ma nelle funzioni COMMIT e ROLLBACK sono vietati. Se ci provi — ottieni: ERROR: invalid transaction termination in function

Questo significa che la funzione deve essere deterministica e sicura, mentre la procedura può fare il "lavoro sporco" — pulire, loggare, inserire.

Tabella comparativa

Dove usare cosa?

Usa una funzione se:

• vuoi un valore di ritorno;
• la chiami in SELECT, filtri dati;
• è un calcolo semplice, un check o un wrapper per SQL.

Usa una procedura se:

• vuoi fare azioni complesse;
• ti serve controllo sulle transazioni;
• gestisci batch, sposti dati, archivi, logghi.