Come restituire valori dalle funzioni in PL/pgSQL

Quando scrivi funzioni in PostgreSQL, una delle prime cose da capire è come restituire un risultato. A volte ti serve solo restituire un numero. Altre volte — una tabella intera. E a volte — anche più set di dati. In questa sezione vediamo tutte le opzioni principali: dal più semplice RETURN a RETURN QUERY, RETURNS TABLE e SETOF.

Un risultato: RETURN

Se la tua funzione deve restituire solo un valore — tipo una somma o il conteggio delle righe — usa semplicemente RETURN.

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

Quando crei una funzione in PL/pgSQL, devi specificare cosa restituisce. Questo riguarda la parola chiave RETURNS, che definisce il "formato" del risultato che la funzione restituisce. Quindi se vuoi restituire un numero, un testo o una tabella di dati — tutto questo va scritto nella riga con RETURNS.

Un esempio semplice:

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

Qui la parola chiave RETURNS INT indica che la funzione restituisce un numero.

Restituire un solo valore

Partiamo dalla cosa più semplice — una funzione che restituisce un solo valore. Per esempio, una funzione che conta il numero di studenti nella tabella students:

CREATE FUNCTION count_students() RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT COUNT(*) INTO total FROM students; -- Salviamo il risultato della query nella variabile total
    RETURN total; -- Restituiamo il risultato
END;
$$ LANGUAGE plpgsql;

Ora possiamo chiamare questa funzione:

SELECT count_students(); -- Restituirà il numero di studenti

Restituire più valori con RETURNS TABLE

A volte hai bisogno di restituire non solo un valore, ma un intero set di record. Per esempio, la lista di tutti gli studenti con i loro nomi e ID. Per questo si usa la costruzione RETURNS TABLE.

CREATE FUNCTION get_students() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM students; -- Restituiamo il risultato della query come tabella
END;
$$ LANGUAGE plpgsql;

Ora possiamo chiamare questa funzione:

SELECT * FROM get_students(); -- Restituirà una tabella con tutti gli studenti

Nota le parole chiave RETURNS TABLE. Indicano che la funzione restituisce una tabella con le colonne specificate (id e name in questo caso).

Uso di RETURN QUERY

Probabilmente ti è già piaciuto l’esempio qui sopra. Ma ecco un altro dettaglio: RETURN QUERY — è la bacchetta magica di PL/pgSQL che ti permette di restituire dati direttamente da una query. Con questa puoi restituire sia il risultato di una query intera che una sua parte.

Supponiamo che dobbiamo restituire tutti gli studenti che stanno studiando attivamente (il loro stato nel database è active = TRUE):

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

Ora possiamo chiamare la funzione e ottenere i dati degli studenti attivi:

SELECT * FROM get_active_students();

Restituire più righe senza RETURNS TABLE

In certi casi potresti voler restituire righe di dati senza usare RETURNS TABLE. Per questo puoi usare il tipo di dato SETOF. Questo ti permette di restituire righe di dati con la stessa struttura. Per esempio:

CREATE FUNCTION get_student_names() RETURNS SETOF TEXT AS $$
BEGIN
    RETURN QUERY
    SELECT name
    FROM students;
END;
$$ LANGUAGE plpgsql;

Questa funzione restituisce solo la lista dei nomi degli studenti:

SELECT * FROM get_student_names();

Restituire valori in base ai parametri di input

Le funzioni non restituiscono sempre solo risultati statici. Possono usare parametri per cambiare dinamicamente i risultati.

Ecco un esempio di restituzione dei dati tramite l’ID dello studente

CREATE FUNCTION get_student_by_id(student_id INT) RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name
    FROM students
    WHERE id = student_id; -- Usiamo il parametro student_id
END;
$$ LANGUAGE plpgsql;

Ora puoi chiedere informazioni su uno studente specifico:

SELECT * FROM get_student_by_id(3); -- Restituirà i dati dello studente con ID = 3

Restituire dati complessi (più set)

A volte i dati sono così complessi che devi restituirli in più set. Per questo puoi usare i cursori. Per esempio, se vuoi fornire due set di dati da una funzione — la lista degli studenti attivi e quella degli studenti non attivi.

CREATE FUNCTION get_students_status() RETURNS SETOF RECORD AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, 'active' AS status
    FROM students
    WHERE active = TRUE;

    RETURN QUERY
    SELECT id, name, 'inactive' AS status
    FROM students
    WHERE active = FALSE;
END;
$$ LANGUAGE plpgsql;

Ora puoi ottenere entrambi i set di dati:

SELECT * FROM get_students_status();

Errori tipici quando lavori con RETURNS

Non specificare il tipo di ritorno: Se non specifichi cosa restituisce la funzione, PostgreSQL darà errore. Per esempio:

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

Tipi di dato non corrispondenti: assicurati che i valori restituiti corrispondano ai tipi dichiarati. Per esempio, se hai scritto che restituisci INT, non provare a restituire una stringa.

Dimenticare di usare RETURN QUERY: se ti dimentichi di usare RETURN QUERY per una query complessa, la funzione semplicemente non restituirà nulla.

Restituzione errata di più valori: se restituisci una riga di dati ma dimentichi di usare SETOF o TABLE, PostgreSQL darà errore.