Chiamare funzioni dalle query SQL

Immagina di avere una funzione per calcoli complessi o per la manipolazione dei dati. Senza la possibilità di integrare la funzione nelle query SQL, lavoreresti così:

1. Chiami la funzione da un linguaggio di programmazione (tipo Python o JavaScript).
2. Passi il risultato alla query SQL.

È un passaggio in più! In PostgreSQL puoi integrare le funzioni direttamente nella query SQL, riducendo il codice, velocizzando le operazioni e diminuendo le chiamate al server. È super utile per:

• Automatizzare i calcoli.
• Validare i dati prima dell'inserimento.
• Modificare dati già esistenti.

Chiamare funzioni in SELECT

Partiamo dalle basi e vediamo come usare le funzioni in una normale query SELECT. Supponiamo di avere una tabella students con info sugli studenti. Vogliamo scrivere una funzione che restituisce l'età attuale dello studente in base alla sua data di nascita.

Passo 1: scrivere la funzione

Creiamo la funzione calculate_age, che prende la data di nascita e restituisce l'età:

CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE) RETURNS INT AS $$
BEGIN
    RETURN DATE_PART('year', AGE(NOW(), birth_date))::INT;
END;
$$ LANGUAGE plpgsql;

Passo 2: usare la funzione nella query SELECT

Ora possiamo chiamare questa funzione per ogni riga della tabella:

SELECT id, name, calculate_age(birth_date) AS age FROM students;

Cosa succede?

• Per ogni riga della tabella students la funzione calculate_age calcola l'età.
• Il valore restituito viene mostrato nella colonna age.

Esempio di risultato:

Come vedi, niente di complicato, e il risultato è bello e professionale.

Chiamare funzioni in INSERT

Le funzioni sono utili anche quando inserisci dati. Per esempio, supponiamo di avere una tabella logs dove vengono registrate le azioni degli utenti. Vogliamo inserire un messaggio di log usando una funzione che genera il testo in automatico.

Passo 1: creare la funzione

Scriviamo la funzione generate_log_message, che prende il nome utente e l'azione, e restituisce il testo del messaggio:

CREATE OR REPLACE FUNCTION generate_log_message(username TEXT, action TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN username || ' performed action: ' || action || ' at ' || NOW();
END;
$$ LANGUAGE plpgsql;

Passo 2: usare la funzione in INSERT

Ora inseriamo il messaggio nella tabella logs, chiamando la funzione quando aggiungiamo la riga:

INSERT INTO logs (message)
VALUES (generate_log_message('Otto', 'accesso al sito'));

Risultato:

La funzione fa tutto per noi: si occupa del formato del testo e aggiunge il timestamp. È un ottimo esempio di come le funzioni possono automatizzare le cose noiose.

Chiamare funzioni in UPDATE

Le funzioni possono essere usate anche per modificare i dati in una tabella. Supponiamo di avere la tabella students e vogliamo aggiornare il nome del gruppo usando una funzione per promuoverli al nuovo corso.

Passo 1: creare la funzione

Scriviamo la funzione promote_student, che prende il vecchio gruppo (tipo 101) e restituisce il nuovo (tipo 201):

CREATE OR REPLACE FUNCTION promote_student(old_group TEXT) RETURNS TEXT AS $$
BEGIN
    RETURN '2' || RIGHT(old_group, LENGTH(old_group) - 1);
END;
$$ LANGUAGE plpgsql;

Passo 2: usare la funzione in UPDATE

Aggiorniamo il gruppo di tutti gli studenti:

UPDATE students
SET group_name = promote_student(group_name);

Risultato:

Guarda come la funzione chiamata fa la magia dell'aggiornamento: i vecchi gruppi diventano nuove stringhe.

Chiamare funzioni nelle condizioni WHERE

Le funzioni possono essere usate nelle condizioni di filtro. Vediamo un esempio con l'età degli studenti.

Passo 1: filtrare per età

Usiamo la funzione calculate_age già creata per selezionare gli studenti con più di 20 anni:

SELECT id, name, birth_date
FROM students
WHERE calculate_age(birth_date) > 20;

Risultato:

Qui il lavoro grosso lo fa la funzione, che calcola l'età di ogni studente al volo.

Combinare con funzioni aggregate

Complichiamo un po' la vita. Dobbiamo contare il numero totale di studenti con meno di 22 anni. Le funzioni si combinano benissimo con le funzioni aggregate come COUNT().

SELECT COUNT(*)
FROM students
WHERE calculate_age(birth_date) < 22;

Cosa succede?

• La funzione calculate_age viene usata nel filtro.
• COUNT(*) conta le righe che rispettano la condizione.

Esempi reali di utilizzo

Automatizzare la validazione dei dati. Supponiamo di voler controllare che l'età di tutti gli studenti sia in un range tipico (tipo da 18 a 30 anni). Scrivi una funzione di controllo e usala nella condizione WHERE.

SELECT id, name
FROM students
WHERE NOT (calculate_age(birth_date) BETWEEN 18 AND 30);

Ottimizzare l'inserimento dei dati. Immagina di lavorare in un e-commerce. Invece di calcolare il totale dell'ordine lato client, scrivi una funzione che lo calcola direttamente quando aggiungi i dati nella tabella orders.

INSERT INTO orders (user_id, total_price)
VALUES (1, calculate_total_price(ARRAY[5, 10, 15]));

Errori tipici quando chiami funzioni

Quando inizi a usare le funzioni nelle query, possono capitare errori. Ecco alcune situazioni comuni e come risolverle:

Mancanza dei permessi necessari. Se non sei il proprietario della funzione o della tabella, PostgreSQL potrebbe bloccare la chiamata. Assicurati di avere i permessi per eseguire la funzione.

Tipi non corrispondenti. Quando passi argomenti alla funzione, occhio ai tipi di dato. Per esempio, se la funzione si aspetta un DATE e tu passi una stringa, avrai errore. Usa la conversione esplicita:

SELECT calculate_age('2000-01-01'::DATE);

Errori di sintassi dentro le funzioni. Se la funzione restituisce errore, può bloccare tutta la query. Testa bene le funzioni prima di usarle.