Automatyczne generowanie raportów według harmonogramu

Kiedy pracujesz z małymi bazami danych, nie ma nic złego w ręcznym odpalaniu zapytań czy procedur do budowy raportów. Ale w prawdziwym świecie bazy danych rosną do takich rozmiarów, że każdą powtarzalną robotę trzeba zautomatyzować. Wyobraź sobie, że codziennie ktoś prosi cię o przygotowanie raportu sprzedaży. Nawet jeśli zapytanie do bazy trwa dwie minuty, przez rok stracisz ponad 12 godzin na jego wykonanie. Lepiej spędzić ten czas przy kawie, a niech automatyczna procedura zrobi to za ciebie.

Automatyzacja pomoże ci:

• Zredukować ręczną robotę.
• Zadbać o regularność raportów (np. codzienne, cotygodniowe raporty).
• Zminimalizować ryzyko błędów przez czynnik ludzki.
• Zwiększyć zaufanie do twoich raportów: zawsze powstają według ustalonych parametrów.

Podstawowe kroki automatycznego generowania raportów

Automatyczne wykonywanie raportów obejmuje takie etapy:

1. Stworzenie procedury w PL/pgSQL, która generuje raport.
2. Ustawienie logowania wyników (jeśli trzeba).
3. Użycie schedulera zadań, żeby odpalać procedurę według harmonogramu.

To co, robimy to krok po kroku!

Tworzenie procedury do generowania raportu

Na początek zrobimy prostą procedurę, która policzy całkowity przychód ze wszystkich zamówień z bieżącego dnia i zapisze wynik do tabeli logów. Nasza tabela do logowania już jest (nazwijmy ją sales_report_log):

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

Teraz tworzymy procedurę w PL/pgSQL:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- Policz całkowity przychód za bieżący dzień
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'Raport za % został pomyślnie utworzony', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

Co tu się dzieje:

• Używamy funkcji agregującej SUM(), żeby policzyć całkowity przychód z tabeli orders.
• Data raportu (report_date) zawsze równa się bieżącej dacie (CURRENT_DATE).
• Wynik zapisujemy do tabeli sales_report_log.
• Komunikat RAISE NOTICE dodany do debugowania: informuje, że raport został utworzony.

Testowanie procedury

Zanim zautomatyzujesz wykonywanie tej procedury, zawsze warto przetestować ją ręcznie. Odpal funkcję:

SELECT generate_daily_sales_report();

A teraz sprawdź zawartość tabeli sales_report_log:

SELECT * FROM sales_report_log;

Jeśli widzisz wiersz z dzisiejszą datą i poprawną wartością przychodu — gratulacje, twoja funkcja działa!

Automatyzacja zadań w PostgreSQL

Czasem fajnie, żeby baza danych sama coś robiła: odpalała raporty, czyściła stare rekordy albo aktualizowała agregaty według harmonogramu. PostgreSQL pozwala to ogarnąć przez rozszerzenie pg_cron albo zewnętrzny scheduler — systemowy cron lub Task Scheduler.

Jeśli pracujesz na Linuxie, najlepszym wyborem będzie pg_cron. To rozszerzenie odpala SQL bezpośrednio w PostgreSQL, bez potrzeby używania shelli czy skryptów.

Zainstaluj pg_cron tak (nie zapomnij podmienić XX na swoją wersję PostgreSQL):

sudo apt install postgresql-XX-cron

Po instalacji trzeba podpiąć je w konfiguracji. Otwórz postgresql.conf i dodaj linię:

shared_preload_libraries = 'pg_cron'

Potem zrestartuj PostgreSQL i aktywuj rozszerzenie w swojej bazie:

CREATE EXTENSION pg_cron;

Teraz możesz zaplanować zadanie. Na przykład, odpalenie funkcji generate_daily_sales_report() codziennie o północy:

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

Tu:

• 'daily_sales_report' — nazwa zadania;
• '0 0 * * *' — harmonogram w stylu cron (tu: codziennie o 00:00);
• SQL między $$ — kod, który będzie wykonany.

Żeby zobaczyć wszystkie zaplanowane zadania, użyj:

SELECT * FROM cron.job;

Jeśli używasz Windowsa albo macOS, to pg_cron albo w ogóle nie działa (Windows), albo wymaga ręcznej kompilacji ze źródeł (macOS). To niewygodne, więc w większości przypadków lepiej użyć systemowego schedulera.

Jak to zrobić:

1. Stwórz plik SQL z potrzebną komendą:

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

2. Użyj psql, żeby wykonać plik:

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

3. Dodaj odpalenie tej komendy do schedulera:

   • Na Linuxie/macOS: przez crontab -e:

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

   • Na Windowsie: przez Task Scheduler, tworząc zadanie, które odpala psql.exe z odpowiednimi parametrami.

• Jeśli jesteś na Linuxie, używaj pg_cron — jest wygodny i wbudowany w PostgreSQL.
• Jeśli jesteś na Windowsie albo Macu, lepiej polegać na systemowym schedulerze (cron albo Task Scheduler) i odpalać SQL przez psql.

Dzięki temu możesz zautomatyzować dowolne zadania w PostgreSQL bez zbędnego kombinowania.

Przykłady automatycznego raportowania

1. Codzienny raport po regionach

Załóżmy, że chcesz automatycznie tworzyć raport przychodu w każdym regionie. Możesz rozbudować naszą funkcję:

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 'Raport regionalny za % został pomyślnie utworzony', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. Miesięczny raport

Podobnie możesz zrobić procedurę do generowania raportu miesięcznego. Po prostu zmień filtr w zapytaniu:

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

Typowe błędy i jak ich unikać

Przy automatycznym generowaniu raportów mogą pojawić się problemy:

• Błąd składni w funkcji: zawsze testuj funkcje ręcznie przed automatyzacją.
• Częstotliwość wykonywania zadań: jeśli zadanie odpala się za często, może przeciążyć bazę. Ustawiaj harmonogram z głową.
• Duplikacja danych: jeśli raport odpala się kilka razy dziennie, mogą pojawić się duplikaty. Używaj unikalnych kluczy, żeby temu zapobiec.

Ten wykład pokazał, jak ustawić automatyczne generowanie raportów w PostgreSQL. Teraz możesz zoptymalizować swoje procesy analityczne i mieć więcej czasu na ważne rzeczy... na przykład szukanie bugów, pisanie kodu albo marzenie o perfekcyjnych zapytaniach SQL.