Tutorial con esempi di stored procedure in PostgreSQL

Introduzione

Le stored procedure in PostgreSQL hanno un ruolo importante e offrono diversi vantaggi nella gestione dei database. In questo tutorial vedremo come usare le stored procedure in PostgreSQL per gestire le operazioni SQL in modo più efficiente e sicuro. L'articolo è pensato per software engineer e amministratori di database che vogliono migliorare le proprie competenze sfruttando le funzionalità più avanzate di PostgreSQL.

Prima di continuare a leggere, volevo parlarti della nostra GUI open source per PostgreSQL, Beekeeper Studio. Mostra le tue stored procedure e funzioni nella barra laterale, così vedi sempre cosa c’è nel tuo database, ed è gratuita per PostgreSQL: scaricala qui.

Cos'è una stored procedure?

Prima di passare agli esempi, è importante capire il concetto di stored procedure. In PostgreSQL, una stored procedure è un oggetto eseguibile precompilato che contiene una serie di comandi SQL. Il suo compito principale è eseguire operazioni sul database come insert, update, delete e create. Sono particolarmente utili per operazioni che prevedono più passaggi o logiche complesse, perché permettono una migliore gestione delle transazioni e riducono la vulnerabilità del database.

Nota: gli esempi e i comandi di questo tutorial sono compatibili con PostgreSQL 11 e versioni successive.

Prerequisiti

Per seguire questo tutorial, devi avere installato e configurato quanto segue:

1. Un'installazione di PostgreSQL sulla tua macchina locale o su un server.
2. Uno strumento da riga di comando o una GUI come Beekeeper Studio per eseguire i comandi SQL.

Creare una stored procedure in PostgreSQL

Storicamente, PostgreSQL ha usato la parola chiave FUNCTION per definire routine che possono restituire un valore. Le funzioni in PostgreSQL possono eseguire operazioni e devono restituire un valore o un insieme, che può essere un valore scalare o una tabella. Possono anche essere usate all'interno delle query SQL.

PROCEDURE, introdotta in PostgreSQL 11, serve a definire routine che non restituiscono un valore. Le procedure possono essere usate per eseguire azioni come modificare lo stato del database senza restituire direttamente dati. Si invocano con l'istruzione CALL, a differenza delle funzioni, che spesso vengono invocate all'interno di un'istruzione SELECT.

Usare PROCEDURE per aggiornare una tabella

Iniziamo creando una semplice stored procedure in PostgreSQL. Creeremo una procedura per aggiornare lo stipendio di un dipendente.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

Nel codice qui sopra, update_employee_salary è il nome della procedura, che accetta due parametri: l'ID del dipendente (p_id) e il nuovo stipendio (p_salary).

Chiamare una stored procedure in PostgreSQL

Per chiamare la procedura appena creata, usiamo il comando CALL come mostrato qui sotto:

CALL update_employee_salary(1, 50000);

Il comando qui sopra aggiornerà a 50000 lo stipendio del dipendente con ID 1.

Uso avanzato: procedure con logica condizionale

Le procedure possono anche usare logiche più complesse, come istruzioni condizionali e gestione degli errori, per eseguire operazioni più sofisticate.

Questa procedura aggiorna il reparto di un dipendente in base alle condizioni fornite, mostrando l'uso delle strutture di controllo all'interno delle procedure.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Output atteso:

CREATE PROCEDURE

Usare FUNCTION per recuperare dati

A differenza di PROCEDURE, una FUNCTION in PostgreSQL è più adatta alle operazioni in cui ci si aspetta un valore di ritorno. Può essere particolarmente utile per recuperare dati ed eseguire calcoli che devono far parte delle query SQL. Qui creeremo un esempio di funzione che recupera informazioni in base a condizioni specifiche.

Questa funzione recupererà i dettagli di un dipendente in base al suo ID. Mostrerà come le funzioni possono restituire dati da usare poi direttamente nelle query SQL.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Output atteso:

CREATE FUNCTION

Invocare la funzione

Per recuperare i dati con questa FUNCTION, usi l'istruzione SELECT, proprio come quando interroghi una tabella.

SELECT * FROM get_employee_details(1);

Output atteso:

In questo caso, l'output sarà costituito dai dettagli del dipendente con ID 1. Se il dipendente esiste, verranno mostrati nome, cognome e ID del reparto. Se non esiste, il risultato sarà vuoto.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Conclusione

In questo tutorial abbiamo visto come scrivere stored procedure in PostgreSQL usando PROCEDURE e FUNCTION e come invocarle. Saper usare le stored procedure aiuta a organizzare meglio il codice, migliorare la sicurezza e la velocità di esecuzione. Questi esempi sono semplici, ma i concetti restano gli stessi anche per operazioni più complesse.

Le stored procedure in PostgreSQL offrono un modo solido per incapsulare la logica, garantendo che le operazioni vengano eseguite in modo efficiente e sicuro. Integrandole nella progettazione del tuo database, puoi ottenere applicazioni più manutenibili e affidabili. Usa questa guida come punto di partenza per esplorare le ampie possibilità offerte dalle funzionalità procedurali di PostgreSQL.