Chiamate annidate di procedure con EXECUTE: esecuzione dinamica di codice SQL

Prima di passare alla pratica, rispondiamo a una domanda: che cos'è SQL dinamico? Immagina che tu debba creare una tabella con un nome unico passato come parametro. Oppure eseguire una query su una tabella il cui nome viene deciso a runtime. Qui il semplice SQL statico non basta — ed è proprio qui che entra in gioco l'esecuzione dinamica.

PL/pgSQL ti mette a disposizione il comando EXECUTE, che esegue una query SQL passata come stringa. Questo ti permette di costruire ed eseguire codice SQL "al volo", creando query che cambiano in base ai parametri.

Ecco perché SQL dinamico può essere utile:

1. Flessibilità: Possibilità di costruire query dinamicamente in base ai dati in ingresso. Ad esempio, operare su tabelle o colonne i cui nomi non sono noti in anticipo.
2. Automazione: Creazione di tabelle o indici con nomi unici.
3. Universalità: Possibilità di lavorare con strutture dati diverse senza dover riscrivere la procedura.

Esempio dalla vita reale: immagina di sviluppare un sistema di analytics e per ogni nuovo cliente devi creare una tabella separata per i suoi dati. Tutto questo si può automatizzare con EXECUTE.

Sintassi di EXECUTE

L'uso di SQL dinamico tramite EXECUTE è così:

EXECUTE 'stringa-SQL';

Esempio di query semplice:

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

Questo blocco di codice crea la tabella test_table. Facile, ma vediamo ora scenari più avanzati.

Esempi di utilizzo di EXECUTE

1. Creazione di una tabella con nome dinamico

Supponiamo che tu debba creare tabelle con nomi che dipendono dalla data corrente. Ecco come si fa:

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- Generiamo il nome della tabella
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- Creiamo la tabella con nome dinamico
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- Mostriamo un messaggio di conferma
  RAISE NOTICE 'Tabella % creata con successo', table_name;
END $$;

Qui il nome dinamico viene generato dalla data corrente e la stringa SQL finale viene passata a EXECUTE.

2. Esecuzione di una query con parametri dinamici

Mettiamo che tu debba estrarre dati da una tabella il cui nome viene passato come parametro. Creiamo una funzione per questo:

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

Chiamata della funzione:

SELECT * FROM get_data_from_table('employees');

Questo approccio è perfetto per costruire utility universali, come sistemi di report dinamici.

Problemi e limiti di SQL dinamico

L'esecuzione dinamica di codice SQL ti dà molta libertà, ma — come nella vita — la libertà comporta responsabilità. Ecco dove possono sorgere problemi:

1. SQL injection: se passi parametri stringa nella query senza filtrarli, dai la possibilità a un malintenzionato di eseguire codice SQL arbitrario.

   Esempio di codice vulnerabile:

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   Se user_input contiene '; DROP TABLE users; --, la query distruggerà la tabella users.

2. Difficoltà di debug: il codice dinamico è più difficile da analizzare e fare debug, perché la query viene costruita ed eseguita a runtime.

3. Perdita di performance: le query dinamiche saltano i meccanismi di caching del piano di esecuzione in PostgreSQL, il che può rallentare le prestazioni.

Come difendersi dalle SQL injection

Per evitare attacchi di SQL injection, usa la parametrizzazione nelle query dinamiche invece della semplice concatenazione di stringhe. In PL/pgSQL si fa con la funzione quote_literal() per i parametri stringa e quote_ident() per identificatori (come nomi di tabelle o colonne).

Esempio di codice sicuro:

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

Implementazione: aggiornamento dinamico delle tabelle

Ecco un esempio di procedura che aggiorna valori in una tabella il cui nome viene passato come parametro:

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

Chiamata della funzione:

SELECT update_table_data('test_table', 1, 'Valore aggiornato');

Esempio: creazione di un report per il cliente

Supponiamo che tu tenga traccia degli ordini per cliente e voglia automatizzare la creazione della tabella report per ogni cliente.

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- Formiamo il nome della tabella report
  table_name := 'client_report_' || client_id;

  -- Creiamo la tabella per il report
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- Popoliamo la tabella con i dati
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE 'Report per il cliente % creato: tabella %', client_id, table_name;
END $$ LANGUAGE plpgsql;

SQL dinamico con EXECUTE è uno strumento potente che apre possibilità incredibili per automazione e flessibilità in PL/pgSQL. Usalo con attenzione, ricordando i rischi di SQL injection. Se vuoi che le tue query siano affidabili e sicure, usa sempre quote_ident() e quote_literal().

Nella prossima lezione approfondiremo la creazione di procedure complesse che includono validazione dei dati, aggiornamento dei record e logging delle operazioni. Preparati: lavorare con query dinamiche sarà la base per realizzare questi task!

1

Sondaggio/quiz

Transazioni annidate, livello 53, lezione 4

Non disponibile

Transazioni annidate

Transazioni annidate

Apri