排程自動產生報表

如果你只是在玩小型資料庫，手動跑 query 或 procedure 來產生報表其實沒什麼大不了。但現實世界的資料庫會長到一個地步，重複性的工作都該自動化。想像一下，每天都有人要你準備一份銷售報表。就算 query 只跑兩分鐘，一年下來你也會花超過 12 小時 在這件事上。這些時間還不如拿去喝咖啡，讓自動 procedure 幫你搞定一切。

自動化可以幫你：

• 減少手動操作。
• 確保報表定時產生（像是每日、每週報表）。
• 把人為出錯的機率降到最低。
• 讓大家更信任你的報表：因為它們都是照設定自動產生的。

自動產生報表的基本步驟

自動執行報表大致分這幾步：

1. 用 PL/pgSQL 寫一個 procedure 來產生報表。
2. 設定結果 log（如果需要）。
3. 用排程器定時執行這個 procedure。

我們來一步一步實作吧！

寫一個產生報表的 procedure

先來寫個簡單的 procedure，計算今天所有訂單的總營收，然後把結果存進 log table。我們的 log table 已經有了（就叫 sales_report_log）：

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

接下來用 PL/pgSQL 寫 procedure：

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- 計算今天的總營收
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE '報表 % 已成功產生', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

這裡發生了什麼：

• 我們用 SUM() 聚合 function 算出 orders table 的總營收。
• 報表日期（report_date）永遠是今天（CURRENT_DATE）。
• 結果存進 sales_report_log。
• 加了 RAISE NOTICE 方便 debug：它會告訴你報表已經產生。

測試 procedure

在自動化執行這個 procedure 之前，手動測試一下總是好主意。來執行 function：

SELECT generate_daily_sales_report();

然後查一下 sales_report_log table 的內容：

SELECT * FROM sales_report_log;

如果你看到有今天日期和正確的總營收——恭喜，function 沒問題！

在 PostgreSQL 裡自動化任務

有時候讓資料庫自己動手做事很方便：像是自動產生報表、清理舊資料、或定時更新 aggregate。PostgreSQL 可以用 pg_cron extension 或外部排程器（系統 cron 或 Task Scheduler）來搞定。

如果你用 Linux，最推薦 pg_cron。這個 extension 直接在 PostgreSQL 裡跑 SQL，不用碰 shell 或 script。

安裝 pg_cron 很簡單（記得把 XX 換成你的 PostgreSQL 版本）：

sudo apt install postgresql-XX-cron

裝好後要在 config 裡啟用。打開 postgresql.conf 加這一行：

shared_preload_libraries = 'pg_cron'

然後重啟 PostgreSQL，並在你的 database 裡啟用 extension：

CREATE EXTENSION pg_cron;

現在可以排任務了。比如每天凌晨零點跑 generate_daily_sales_report()：

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

這裡：

• 'daily_sales_report' — 任務名稱；
• '0 0 * * *' — cron 格式的排程（這裡是每天 00:00）；
• 夾在 $$ 裡的 SQL 就是要執行的 code。

要看所有排程任務，用這個：

SELECT * FROM cron.job;

如果你用 Windows 或 macOS，pg_cron 不是完全支援（Windows 完全不支援，macOS 要自己編譯）。這樣很麻煩，大多數情況直接用系統排程器比較快。

做法如下：

1. 先寫一個 SQL 檔，裡面放你要執行的指令：

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

2. 用 psql 執行這個檔案：

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

3. 把這個指令加到排程器：

   • 在 Linux/macOS：用 crontab -e：

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

   • 在 Windows：用 Task Scheduler，建立一個任務去跑 psql.exe 並帶上參數。

• 如果你在 Linux，用 pg_cron 最方便，直接內建 PostgreSQL。
• 如果你在 Windows 或 Mac，建議用系統排程器（cron 或 Task Scheduler）配合 psql 跑 SQL。

這樣你就能輕鬆自動化 PostgreSQL 的各種任務啦。

自動報表的範例

1. 每日區域報表

假設你想自動產生每個區域的營收報表，可以這樣擴充 function：

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 '區域報表 % 已成功產生', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. 每月報表

同理，也可以寫個 procedure 產生月報。只要改一下查詢的條件：

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

常見錯誤與防呆

自動產生報表時可能會遇到這些問題：

• function 語法錯誤：自動化前一定要手動測試 function。
• 任務執行太頻繁：排程太密會拖垮資料庫，請合理設定。
• 資料重複：一天跑多次可能會有重複資料。建議用 unique key 防止重複。

這堂講座教你怎麼在 PostgreSQL 裡自動產生報表。現在你可以優化自己的分析流程，把時間省下來做更重要的事……比如抓 bug、寫 code，或是幻想你的 SQL query 有多完美。