PL/pgSQL 的主要功能

好啦，讓我們來深入聊聊 PL/pgSQL 為什麼這麼強大，對資料庫開發者和管理員來說根本是不可或缺的工具。在這堂課我們會聊 PL/pgSQL 的優勢、它獨特的功能，還有一些實例，讓你看看這些功能在現實生活中到底有多實用。

為了理解為什麼我們需要 PL/pgSQL，想像一下，如果你在一個所有程式任務都只能用 SQL 解決的世界。比如說，你要算每個學院的學生數量，你就得寫一個超複雜的 SQL 查詢，然後還要在 client 端處理結果。這樣其實很沒效率對吧？這時候 PL/pgSQL 就出場啦，它支援變數、迴圈、條件判斷還有錯誤處理。

使用 PL/pgSQL 的好處：

1. 伺服器端邏輯：PL/pgSQL 讓你可以把邏輯都放在伺服器端執行，減少 server 跟 client 之間的資料傳輸，這樣網路延遲就會降低。
2. 效能：PL/pgSQL 的 function 會被編譯並存在資料庫裡，執行速度比一堆獨立 SQL 查詢快很多。
3. 任務自動化：用 PL/pgSQL 可以自動化一些日常操作，比如資料更新、紀錄 log 或檢查資料完整性。
4. 商業邏輯：PL/pgSQL 可以實現複雜的商業邏輯，像是計算、驗證或產生分析報表。
5. 方便又好讀：PL/pgSQL 的 code 很容易結構化、拆成 function 來維護，寫起來也很舒服。

PL/pgSQL 的應用領域

現在我們來看看 PL/pgSQL 到底可以用在哪裡，以及它怎麼解決實際問題。

1. 自動化日常操作

PL/pgSQL 可以自動化重複性的任務。比如說，你需要每天更新某些資料，或定期跑分析。寫個 PL/pgSQL function，再配合任務排程器（像 pg_cron）就能在指定時間自動執行。

範例：自動更新狀態

CREATE FUNCTION update_student_status() RETURNS VOID AS $$
BEGIN
    UPDATE students
    SET status = '不活躍'
    WHERE last_login < NOW() - INTERVAL '1 year';
    RAISE NOTICE '學生狀態已更新。';
END;
$$ LANGUAGE plpgsql;

這個 function 會自動把超過一年沒登入系統的學生狀態設成 "不活躍"。

2. 產生報表

PL/pgSQL 很適合做分析報表，尤其是需要從多個 table 聚合資料時。你可以寫 procedure 自動產生報表，然後把結果存到專門的 table。

範例：依學院產生學生數量報表

CREATE FUNCTION generate_faculty_report() RETURNS TABLE (faculty_id INT, student_count INT) AS $$
BEGIN
    RETURN QUERY
    SELECT faculty_id, COUNT(*)
    FROM students
    GROUP BY faculty_id;
END;
$$ LANGUAGE plpgsql;

呼叫這個 function 就能拿到所有學院的統計數據。

3. 記錄 table 變更

Log 記錄就是把 table 裡的資料變更寫下來。PL/pgSQL 可以很有效率地做到這件事，比如用 trigger。

範例：變更記錄 function

CREATE FUNCTION log_changes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO change_logs(table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, ROW_TO_JSON(OLD), ROW_TO_JSON(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

這個 function 會把哪個 table 被改、做了什麼操作（像 INSERT、UPDATE、DELETE），還有舊資料跟新資料都記錄到 change_logs table 裡。

4. 實現複雜演算法

用 PL/pgSQL 你可以寫出超越標準 SQL 能力的演算法。比如成本計算、商業規則驗證或自動產生識別碼。

範例：產生唯一識別碼

CREATE FUNCTION generate_unique_id() RETURNS TEXT AS $$
BEGIN
    RETURN CONCAT('UID-', EXTRACT(EPOCH FROM NOW()), '-', RANDOM()::TEXT);
END;
$$ LANGUAGE plpgsql;

這個 function 會產生一個唯一識別碼，內容包含目前的時間戳和隨機數。

5. 和 trigger 一起用

Trigger 跟 PL/pgSQL 根本是絕配。當你想自動處理某些事，比如同步更新相關資料，用 PL/pgSQL function 搭配 trigger 超方便。

範例：刪除學生時的 trigger

CREATE FUNCTION handle_delete_students() RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM enrollments WHERE student_id = OLD.id;
    RAISE NOTICE '已刪除學生 % 的選課紀錄。', OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

用這個 function，你可以在 students table 刪除學生時，自動把 enrollments table 裡對應的選課紀錄也刪掉。

6. 錯誤處理

遇到複雜任務時，錯誤處理超重要。PL/pgSQL 提供 EXCEPTION 區塊，讓你可以攔截和處理錯誤。

範例：錯誤處理

CREATE FUNCTION insert_student(name TEXT, faculty_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO students(name, faculty_id) VALUES (name, faculty_id);
EXCEPTION
    WHEN FOREIGN_KEY_VIOLATION THEN
        RAISE NOTICE '學院 ID % 不存在！', faculty_id;
END;
$$ LANGUAGE plpgsql;

這裡如果你輸入的學院 ID 在資料庫裡找不到，就會顯示警告訊息，而不是直接報錯。

PL/pgSQL 能解決的複雜任務範例

為了讓你更有動力學 PL/pgSQL，這裡有幾個它能搞定的任務範例：

1. 網路商店自動更新折扣 每天自動更新那些快到期商品的折扣。

2. 資料檢查與修正 檢查 table 有沒有重複資料，然後自動刪掉。

3. 快速切換設定 讓你可以一鍵切換系統參數，比如改變 app 的運作模式。

IT 世界的真實案例

PL/pgSQL 被全球上百萬家公司使用。舉例來說：

• 網路商店 用 function 來計算稅金、自動更新折扣和產生銷售報表。
• 銀行 用 PL/pgSQL 處理每天成千上萬的交易，從利息計算到信用評分檢查。
• 社群網站 實作複雜的資料處理演算法，比如推薦朋友。

PL/pgSQL 就像是 PostgreSQL 開發者的瑞士刀。不只讓你操作資料庫更簡單，還能搞定那些用純 SQL 很難甚至做不到的事。最重要的是——PL/pgSQL 超好學，學會之後你就能變成資料庫高手啦！