Creazione di funzioni semplici: CREATE FUNCTION

In PostgreSQL le funzioni sono uno strumento potente che ti permette di automatizzare compiti, creare business logic e dare al server un po' più di intelligenza. Pensa alle funzioni come a mini-programmi che girano dentro il database. Sono utili per:

• Riutilizzo del codice. Se ti capita di scrivere le stesse query più volte, mettile in una funzione e chiamala quando ti serve.
• Automatizzare i compiti. Tipo, devi calcolare lo stipendio dei dipendenti in base alle ore lavorate? Una funzione lo fa al volo.
• Incasulamento della logica. Così lasci i calcoli complicati al server e i client non devono impazzire con le query SQL.

Sintassi generale di CREATE FUNCTION

Ecco come si presenta la struttura base per creare una funzione:

CREATE FUNCTION function_name(parameters) RETURNS return_type AS $$
BEGIN
    -- Corpo della funzione (logica)
    RETURN risultato;
END;
$$ LANGUAGE plpgsql;

Vediamo le parti principali:

CREATE FUNCTION function_name(parameters):

Qui dai il nome alla funzione function_name e specifichi i parametri (se servono).

I parametri possono avere nome e tipo: my_param INTEGER, another_param TEXT.

RETURNS return_type:

Specifichi cosa restituirà la funzione: un valore (INTEGER, TEXT ecc.) o un set di dati (TABLE, RECORD).

BEGIN ... END:

Tra queste parole chiave c'è il "corpo" della funzione, dove succede tutta la magia.

RETURN risultato:

Restituisce il risultato della funzione. Occhio: il tipo del risultato deve essere quello che hai messo in RETURNS.

LANGUAGE plpgsql:

Specifichi che usi il linguaggio PL/pgSQL. PostgreSQL supporta anche altri linguaggi, ma per ora ci serve questo.

Esempio semplice: somma di due numeri

Facciamo una funzione che restituisce la somma di due numeri interi.

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Ora chiamiamola:

SELECT add_numbers(5, 7); -- Risultato: 12

Cosa succede qui?

• La funzione prende due parametri a e b di tipo INT.
• Dentro la funzione li sommiamo (a + b) e restituiamo il risultato.
• Facile come una calcolatrice!

Esempio con uso di variabili

Immagina di avere un database universitario e vuoi sapere quanti studenti sono iscritti.

Creiamo una funzione:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT; -- Dichiariamo una variabile per il risultato
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Contiamo le righe nella tabella
    RETURN total; -- Restituiamo il risultato
END;
$$ LANGUAGE plpgsql;

Chiamata della funzione:

SELECT count_students(); -- Supponiamo, risultato: 120

Qui vediamo:

• L'uso della variabile total per salvare il risultato della query SQL.
• Il comando SELECT ... INTO mette il risultato della query nella variabile.

Questo è comodo se devi prima lavorare sui dati e poi restituirli.

Restituire più valori: RETURNS TABLE

Nell'esempio prima restituivamo solo un valore. Ma se la funzione deve restituire un set di dati, tipo una lista di studenti? Qui RETURNS TABLE è super utile.

Esempio:

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students;
END;
$$ LANGUAGE plpgsql;

Chiamata della funzione:

SELECT * FROM get_students();

Possibile risultato:

Utilità di RETURN QUERY per eseguire query dentro la funzione

RETURN QUERY ti permette di restituire direttamente il risultato di una query SQL dalla funzione. Così risparmi passaggi intermedi e le funzioni sono più semplici.

Facciamo una funzione che restituisce solo gli studenti attivi:

CREATE FUNCTION get_active_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students WHERE active = TRUE;
END;
$$ LANGUAGE plpgsql;

Prima di chiamare la funzione get_active_students(), devi creare la tabella students e riempirla con dati di test. Ecco come puoi fare:

-- Creiamo la tabella degli studenti
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- Aggiungiamo qualche record
INSERT INTO students (name, active) VALUES
('Alice', FALSE),
('Bob', TRUE),
('Charlie', TRUE),
('Dana', FALSE);

Tabella:

Ora la chiamata:

SELECT * FROM get_active_students();

Risultato:

Controllo della correttezza dei dati prima di eseguire

Le funzioni possono avere controlli IF per assicurarsi che i dati siano ok. Ad esempio, possiamo creare una funzione per promuovere uno studente al prossimo anno solo se ha passato tutti gli esami.

Esempio:

CREATE FUNCTION promote_student(student_id INT) RETURNS TEXT AS $$
DECLARE
    passed_exams INT;
BEGIN
    -- Contiamo il numero di esami superati dallo studente
    SELECT COUNT(*) INTO passed_exams
    FROM exams
    WHERE student_id = promote_student.student_id AND status = 'passed';

    -- Controlliamo la condizione
    IF passed_exams < 5 THEN
        RETURN 'Lo studente non ha passato abbastanza esami';
    END IF;

    -- Aggiorniamo il corso dello studente
    UPDATE students
    SET course = course + 1
    WHERE id = promote_student.student_id;

    RETURN 'Studente promosso!';
END;
$$ LANGUAGE plpgsql;

Errori tipici nella creazione di funzioni

Manca il tipo di ritorno. PostgreSQL vuole sempre che tu dica che tipo di risultato restituisce la funzione. Tipo:

CREATE FUNCTION fail() AS $$ -- Errore: manca RETURNS
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Correzione:

CREATE FUNCTION succeed() RETURNS INT AS $$
BEGIN
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Tipo di ritorno sbagliato. Se metti RETURNS INT, devi restituire un numero. Provare a restituire una stringa qui è una pessima idea.

Errore nelle query SQL dentro la funzione. Controlla sempre le query prima di usarle nelle funzioni. Meglio testarle "a mano" con psql o pgAdmin.