Generazione automatica dei report secondo una pianificazione

Quando lavori con piccoli database, non è un problema lanciare manualmente query o procedure per creare i report. Ma nel mondo reale i database crescono a tal punto che qualsiasi compito ripetitivo va automatizzato. Immagina che ti chiedano ogni giorno di preparare un report sulle vendite. Anche se la query richiede solo due minuti, in un anno spenderai più di 12 ore solo per questo. Meglio passare quel tempo con un caffè, mentre la procedura automatica fa tutto da sola.

L'automazione ti aiuta a:

• Ridurre il lavoro manuale.
• Garantire la regolarità dei report (ad esempio, report giornalieri, settimanali).
• Minimizzare la probabilità di errori dovuti al fattore umano.
• Aumentare la fiducia nei tuoi report: vengono sempre creati secondo i parametri stabiliti.

Passaggi principali per la generazione automatica dei report

L'esecuzione automatica dei report include questi step:

1. Creare una procedura in PL/pgSQL che genera il report.
2. Configurare il logging dei risultati (se serve).
3. Usare un task scheduler per lanciare la procedura secondo una pianificazione.

Dai, facciamolo passo dopo passo!

Creazione della procedura per generare il report

Per cominciare, creiamo una semplice procedura che calcola il totale delle vendite di tutti gli ordini del giorno corrente e salva il risultato in una tabella di log. La nostra tabella di log esiste già (chiamiamola sales_report_log):

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ora creiamo la procedura in PL/pgSQL:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- Calcolare il totale delle vendite per oggi
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'Report per % creato con successo', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

Cosa succede qui:

• Usiamo la funzione aggregata SUM() per calcolare il totale delle vendite dalla tabella orders.
• La data del report (report_date) è sempre la data corrente (CURRENT_DATE).
• Il risultato viene salvato nella tabella sales_report_log.
• Il messaggio RAISE NOTICE è aggiunto per il debug: ti dice che il report è stato creato con successo.

Test della procedura

Prima di automatizzare l'esecuzione di questa procedura, è sempre utile testarla manualmente. Lanciamo la funzione:

SELECT generate_daily_sales_report();

Ora controlliamo il contenuto della tabella sales_report_log:

SELECT * FROM sales_report_log;

Se vedi una riga con la data di oggi e il valore corretto delle vendite totali — congratulazioni, la tua funzione funziona!

Automatizzare i task in PostgreSQL

A volte è comodo che il database faccia qualcosa da solo: lanciare report, pulire vecchi record o aggiornare aggregati secondo una pianificazione. PostgreSQL permette di farlo con l'estensione pg_cron o con un task scheduler esterno — il cron di sistema o Task Scheduler.

Se lavori su Linux, la scelta migliore è pg_cron. Questa estensione lancia SQL direttamente dentro PostgreSQL, senza bisogno di shell o script.

Per installare pg_cron (ricorda di sostituire XX con la tua versione di PostgreSQL):

sudo apt install postgresql-XX-cron

Dopo l'installazione, devi abilitarlo nella configurazione. Apri postgresql.conf e aggiungi la riga:

shared_preload_libraries = 'pg_cron'

Poi riavvia PostgreSQL e attiva l'estensione nel tuo database:

CREATE EXTENSION pg_cron;

Ora puoi pianificare un task. Ad esempio, lanciare la funzione generate_daily_sales_report() ogni giorno a mezzanotte:

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

Qui:

• 'daily_sales_report' — nome del task;
• '0 0 * * *' — pianificazione in stile cron (in questo caso — ogni giorno alle 00:00);
• SQL tra $$ — il codice che verrà eseguito.

Per vedere tutti i task pianificati, usa:

SELECT * FROM cron.job;

Se usi Windows o macOS, pg_cron o non è supportato (su Windows), o richiede la compilazione manuale dai sorgenti (su macOS). È scomodo, e nella maggior parte dei casi è più semplice usare il task scheduler di sistema.

Ecco come fare:

1. Crea un file SQL con il comando che ti serve:

echo "SELECT generate_daily_sales_report();" > /path/to/script.sql

2. Usa psql per eseguire il file:

psql -h localhost -U postgres -d your_database -f /path/to/script.sql

3. Aggiungi l'esecuzione di questo comando al task scheduler:

   • Su Linux/macOS: tramite crontab -e:

     0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

   • Su Windows: tramite Task Scheduler, creando un task che lancia psql.exe con i parametri giusti.

• Se sei su Linux, usa pg_cron — è comodo e integrato in PostgreSQL.
• Se sei su Windows o Mac, conviene affidarsi al task scheduler di sistema (cron o Task Scheduler) e lanciare SQL tramite psql.

Così puoi automatizzare qualsiasi task in PostgreSQL senza fatica.

Esempi di reportistica automatica

1. Report giornaliero per regione

Supponiamo che tu voglia creare automaticamente un report sulle vendite per ogni regione. Puoi estendere la nostra funzione così:

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE 'Report regionale per % creato con successo', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. Report mensile

Allo stesso modo puoi creare una procedura per generare il report mensile. Basta cambiare il filtro nella query:

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

Errori tipici e come evitarli

Nella generazione automatica dei report possono capitare problemi:

• Errore di sintassi nella funzione: testa sempre le funzioni manualmente prima di automatizzare.
• Frequenza di esecuzione dei task: se il task gira troppo spesso, può sovraccaricare il database. Imposta la pianificazione con criterio.
• Duplicazione dei dati: se il report viene lanciato più volte al giorno, possono esserci duplicati. Usa chiavi uniche per evitarli.

Questa lezione ti ha mostrato come configurare la generazione automatica dei report in PostgreSQL. Ora puoi ottimizzare i tuoi processi analitici, lasciando più tempo per le cose importanti... tipo cercare bug, scrivere codice o sognare la perfezione delle tue query SQL.