PL/pgSQL 程式語言：PostgreSQL 伺服器端程式設計完全指南 | PostgreSQL

[ <<label>> ]
[ DECLARE
    -- 變數宣告
]
BEGIN
    -- 執行邏輯
[ EXCEPTION
    -- 例外處理
]
END [ label ];

DO $$
<<outer_block>>
DECLARE
    v_total INT := 0;
BEGIN
    RAISE NOTICE '外層 total = %', v_total;

    <<inner_block>>
    DECLARE
        v_total INT := 100;  -- 遮蔽外層同名變數
    BEGIN
        RAISE NOTICE '內層 total = %', v_total;       -- 100
        RAISE NOTICE '外層 total = %', outer_block.v_total;  -- 0（用 label 存取）
    END inner_block;

    RAISE NOTICE '回到外層 total = %', v_total;  -- 0
END outer_block;
$$;

DECLARE
    v_id        INT := 0;
    v_name      TEXT;
    v_price     NUMERIC(10,2) := 0.00;
    v_is_active BOOLEAN := TRUE;
    v_created   TIMESTAMPTZ := now();
    v_tags      TEXT[] := '{}';           -- 陣列
    v_config    JSONB := '{}';            -- JSONB

CREATE OR REPLACE FUNCTION get_product_info(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    v_name   products.name%TYPE;       -- 繼承 products.name 的型別
    v_row    products%ROWTYPE;          -- 整行結構
    v_rec    RECORD;                    -- 動態結構
BEGIN
    -- %TYPE：只取一個欄位
    SELECT name INTO v_name FROM products WHERE id = p_id;

    -- %ROWTYPE：取整行
    SELECT * INTO v_row FROM products WHERE id = p_id;

    -- RECORD：動態查詢
    SELECT id, name, price INTO v_rec
    FROM products WHERE id = p_id;

    RETURN format('Name: %s, Price: %s', v_row.name, v_row.price);
END;
$$;

CREATE OR REPLACE FUNCTION classify_score(p_score INT)
RETURNS TEXT
LANGUAGE plpgsql AS $$
BEGIN
    IF p_score >= 90 THEN
        RETURN 'A';
    ELSIF p_score >= 80 THEN
        RETURN 'B';
    ELSIF p_score >= 70 THEN
        RETURN 'C';
    ELSIF p_score >= 60 THEN
        RETURN 'D';
    ELSE
        RETURN 'F';
    END IF;
END;
$$;

-- 簡單 CASE
CASE v_status
    WHEN 'active'   THEN v_label := '啟用';
    WHEN 'inactive' THEN v_label := '停用';
    WHEN 'pending'  THEN v_label := '待審';
    ELSE v_label := '未知';
END CASE;

-- 搜尋 CASE
CASE
    WHEN v_amount > 10000 THEN v_tier := 'VIP';
    WHEN v_amount > 5000  THEN v_tier := 'Gold';
    WHEN v_amount > 1000  THEN v_tier := 'Silver';
    ELSE v_tier := 'Standard';
END CASE;

-- 基本 LOOP（無窮迴圈 + EXIT 條件）
LOOP
    v_counter := v_counter + 1;
    EXIT WHEN v_counter > 10;
END LOOP;

-- WHILE 迴圈
WHILE v_balance > 0 LOOP
    v_balance := v_balance - v_payment;
    v_months := v_months + 1;
END LOOP;

-- FOR 整數範圍
FOR i IN 1..10 LOOP
    RAISE NOTICE 'i = %', i;
END LOOP;

-- FOR 反向
FOR i IN REVERSE 10..1 LOOP
    RAISE NOTICE 'countdown: %', i;
END LOOP;

-- FOR 查詢結果（最常用）
FOR v_rec IN
    SELECT id, name, price FROM products WHERE category = 'Electronics'
LOOP
    RAISE NOTICE 'Product: % ($ %)', v_rec.name, v_rec.price;
END LOOP;

-- FOREACH 陣列元素
DECLARE
    v_tags TEXT[] := ARRAY['urgent', 'important', 'review'];
    v_tag  TEXT;
BEGIN
    FOREACH v_tag IN ARRAY v_tags LOOP
        RAISE NOTICE 'Tag: %', v_tag;
    END LOOP;
END;

FOR v_rec IN SELECT * FROM orders LOOP
    CONTINUE WHEN v_rec.status = 'cancelled';
    -- 處理非取消的訂單
    PERFORM process_order(v_rec.id);
END LOOP;

-- 格式化訊息（% 為佔位符）
RAISE NOTICE '處理訂單 #%，金額 $%', v_order_id, v_amount;

-- 拋出自訂錯誤
RAISE EXCEPTION '無效的折扣率：%（必須在 0-100 之間）', v_discount
    USING ERRCODE = 'check_violation',
          HINT = '請檢查 discount 欄位的值';

-- 使用 SQLSTATE 錯誤碼
RAISE EXCEPTION SQLSTATE '23505'
    USING MESSAGE = '訂單編號重複',
          DETAIL = format('order_no = %s 已存在', v_order_no);

CREATE OR REPLACE FUNCTION safe_transfer(
    p_from_id INT, p_to_id INT, p_amount NUMERIC
)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    v_from_balance NUMERIC;
BEGIN
    -- 檢查餘額
    SELECT balance INTO v_from_balance
    FROM accounts WHERE id = p_from_id FOR UPDATE;

    IF v_from_balance < p_amount THEN
        RAISE EXCEPTION '餘額不足：帳戶 % 餘額 $%，轉帳 $%',
            p_from_id, v_from_balance, p_amount;
    END IF;

    -- 執行轉帳
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

    RETURN 'OK';

EXCEPTION
    WHEN numeric_value_out_of_range THEN
        RETURN '金額超出範圍';
    WHEN undefined_table THEN
        RETURN '表不存在';
    WHEN OTHERS THEN
        -- 捕獲所有未預期的錯誤
        RAISE WARNING '轉帳失敗：% (SQLSTATE: %)', SQLERRM, SQLSTATE;
        RETURN format('ERROR: %s', SQLERRM);
END;
$$;

EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_message    = MESSAGE_TEXT,
            v_detail     = PG_EXCEPTION_DETAIL,
            v_hint       = PG_EXCEPTION_HINT,
            v_context    = PG_EXCEPTION_CONTEXT,
            v_sqlstate   = RETURNED_SQLSTATE;

        INSERT INTO error_log (message, detail, hint, context, sqlstate, created_at)
        VALUES (v_message, v_detail, v_hint, v_context, v_sqlstate, now());

        RAISE;  -- 重新拋出原始錯誤

SELECT name, price INTO v_name, v_price
FROM products WHERE id = p_id;

IF NOT FOUND THEN
    RAISE EXCEPTION '找不到產品 ID: %', p_id;
END IF;

-- STRICT 模式：結果必須恰好一行，否則報錯
SELECT name INTO STRICT v_name
FROM products WHERE category = p_category;
-- 0 行 → NO_DATA_FOUND
-- 多行 → TOO_MANY_ROWS

-- 錯誤示範
SELECT ... INTO v_row FROM table_a WHERE ...;
PERFORM some_function();       -- 這會覆蓋 FOUND！
IF NOT FOUND THEN ...          -- 此 FOUND 是 some_function() 的結果

-- 正確示範
SELECT ... INTO v_row FROM table_a WHERE ...;
IF NOT FOUND THEN              -- 立即檢查
    ...
END IF;
PERFORM some_function();

CREATE OR REPLACE FUNCTION dynamic_count(
    p_table TEXT,
    p_where TEXT DEFAULT NULL
)
RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
    v_sql   TEXT;
    v_count BIGINT;
BEGIN
    v_sql := format('SELECT count(*) FROM %I', p_table);

    IF p_where IS NOT NULL THEN
        v_sql := v_sql || ' WHERE ' || p_where;
    END IF;

    EXECUTE v_sql INTO v_count;
    RETURN v_count;
END;
$$;

-- 使用
SELECT dynamic_count('orders', 'status = ''active''');

-- 推薦寫法：format %I 處理識別符，USING 處理值
CREATE OR REPLACE FUNCTION find_by_column(
    p_table  TEXT,
    p_column TEXT,
    p_value  TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT * FROM %I WHERE %I = $1', p_table, p_column)
        USING p_value;
END;
$$;

-- 危險！SQL Injection 漏洞
v_sql := 'SELECT * FROM ' || p_table || ' WHERE name = ''' || p_name || '''';

-- 安全做法
v_sql := format('SELECT * FROM %I WHERE name = $1', p_table);
EXECUTE v_sql USING p_name;

CREATE FUNCTION get_count() RETURNS BIGINT ...

-- 回傳自訂型別
CREATE TYPE product_summary AS (
    name TEXT,
    total_sales NUMERIC
);

CREATE FUNCTION top_product() RETURNS product_summary ...

CREATE OR REPLACE FUNCTION search_products(p_keyword TEXT)
RETURNS TABLE(id INT, name TEXT, price NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
        SELECT p.id, p.name, p.price
        FROM products p
        WHERE p.name ILIKE '%' || p_keyword || '%'
        ORDER BY p.price DESC;
END;
$$;

-- 使用
SELECT * FROM search_products('laptop');

CREATE OR REPLACE FUNCTION generate_fibonacci(n INT)
RETURNS SETOF BIGINT
LANGUAGE plpgsql AS $$
DECLARE
    a BIGINT := 0;
    b BIGINT := 1;
    temp BIGINT;
BEGIN
    FOR i IN 1..n LOOP
        RETURN NEXT a;
        temp := a + b;
        a := b;
        b := temp;
    END LOOP;
END;
$$;

SELECT * FROM generate_fibonacci(10);

CREATE OR REPLACE FUNCTION dynamic_search(
    p_table TEXT, p_column TEXT, p_value TEXT
)
RETURNS SETOF RECORD
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT * FROM %I WHERE %I = $1', p_table, p_column)
        USING p_value;
END;
$$;

CREATE OR REPLACE FUNCTION process_large_table()
RETURNS VOID
LANGUAGE plpgsql AS $$
DECLARE
    cur CURSOR FOR
        SELECT id, data FROM large_table WHERE processed = FALSE;
    v_rec RECORD;
    v_batch INT := 0;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_rec;
        EXIT WHEN NOT FOUND;

        -- 處理每一行
        UPDATE large_table
        SET processed = TRUE, result = compute(v_rec.data)
        WHERE CURRENT OF cur;  -- 用 Cursor 定位更新

        v_batch := v_batch + 1;
        IF v_batch % 1000 = 0 THEN
            RAISE NOTICE '已處理 % 行', v_batch;
        END IF;
    END LOOP;
    CLOSE cur;

    RAISE NOTICE '處理完成，共 % 行', v_batch;
END;
$$;

CREATE OR REPLACE FUNCTION open_orders_cursor(p_status TEXT)
RETURNS REFCURSOR
LANGUAGE plpgsql AS $$
DECLARE
    cur REFCURSOR := 'orders_cur';
BEGIN
    OPEN cur FOR
        SELECT * FROM orders WHERE status = p_status ORDER BY created_at;
    RETURN cur;
END;
$$;

-- Client 端使用
BEGIN;
SELECT open_orders_cursor('pending');
FETCH 10 FROM orders_cur;   -- 取 10 行
FETCH 10 FROM orders_cur;   -- 再取 10 行
CLOSE orders_cur;
COMMIT;

SET plan_cache_mode = 'force_generic_plan';  -- Session 級設定

-- IMMUTABLE：數學計算
CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
    RETURN amount * 0.05;
END;
$$;

-- STABLE：讀取設定（同一 TX 內不變）
CREATE FUNCTION get_exchange_rate(currency TEXT)
RETURNS NUMERIC
LANGUAGE plpgsql STABLE AS $$
DECLARE v_rate NUMERIC;
BEGIN
    SELECT rate INTO v_rate FROM exchange_rates WHERE code = currency;
    RETURN v_rate;
END;
$$;

-- VOLATILE：有副作用（INSERT/UPDATE/DELETE、使用 random()、clock_timestamp()）
CREATE FUNCTION log_access(p_user_id INT)
RETURNS VOID
LANGUAGE plpgsql VOLATILE AS $$
BEGIN
    INSERT INTO access_log (user_id, accessed_at) VALUES (p_user_id, now());
END;
$$;

-- SQL Function（簡單計算，可被內聯）
CREATE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT
LANGUAGE SQL IMMUTABLE
AS $$ SELECT first || ' ' || last; $$;

-- PL/pgSQL Function（複雜邏輯）
CREATE FUNCTION process_order(p_order_id INT)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE ...
BEGIN
    -- 多步驟流程
    ...
END;
$$;

-- 批次更新範例
DO $$
DECLARE
    v_rec RECORD;
    v_count INT := 0;
BEGIN
    FOR v_rec IN
        SELECT id FROM users WHERE last_login < now() - interval '1 year'
    LOOP
        UPDATE users SET status = 'inactive' WHERE id = v_rec.id;
        v_count := v_count + 1;
    END LOOP;

    RAISE NOTICE '已標記 % 個不活躍使用者', v_count;
END;
$$;

CREATE OR REPLACE PROCEDURE batch_archive_orders(p_batch_size INT DEFAULT 1000)
LANGUAGE plpgsql AS $$
DECLARE
    v_archived INT;
    v_total    INT := 0;
BEGIN
    LOOP
        -- 每批次搬移 p_batch_size 筆
        WITH moved AS (
            DELETE FROM orders
            WHERE status = 'completed'
              AND completed_at < now() - interval '2 years'
            LIMIT p_batch_size
            RETURNING *
        )
        INSERT INTO orders_archive SELECT * FROM moved;

        GET DIAGNOSTICS v_archived = ROW_COUNT;
        v_total := v_total + v_archived;

        -- 每批次後提交，釋放鎖和資源
        COMMIT;
        RAISE NOTICE '已歸檔 % 筆（累計 %）', v_archived, v_total;

        EXIT WHEN v_archived < p_batch_size;  -- 不足一批，處理完畢
    END LOOP;

    RAISE NOTICE '歸檔完成，共 % 筆', v_total;
END;
$$;

-- 呼叫
CALL batch_archive_orders(5000);

CREATE OR REPLACE FUNCTION upsert_setting(
    p_key TEXT, p_value TEXT
)
RETURNS VOID
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO settings (key, value, updated_at)
    VALUES (p_key, p_value, now())
    ON CONFLICT (key) DO UPDATE
        SET value = EXCLUDED.value,
            updated_at = now();
END;
$$;

CREATE OR REPLACE FUNCTION paginate_products(
    p_page     INT DEFAULT 1,
    p_per_page INT DEFAULT 20,
    p_category TEXT DEFAULT NULL
)
RETURNS TABLE(id INT, name TEXT, price NUMERIC, total_count BIGINT)
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
        SELECT
            p.id, p.name, p.price,
            count(*) OVER() AS total_count
        FROM products p
        WHERE (p_category IS NULL OR p.category = p_category)
        ORDER BY p.created_at DESC
        LIMIT p_per_page
        OFFSET (p_page - 1) * p_per_page;
END;
$$;

-- 使用
SELECT * FROM paginate_products(2, 10, 'Electronics');

CREATE OR REPLACE FUNCTION retry_operation(p_max_retries INT DEFAULT 3)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    v_attempt INT := 0;
    v_result  TEXT;
BEGIN
    LOOP
        v_attempt := v_attempt + 1;
        BEGIN
            -- 嘗試執行可能失敗的操作
            PERFORM do_something_risky();
            v_result := 'SUCCESS';
            EXIT;  -- 成功就跳出

        EXCEPTION
            WHEN serialization_failure OR deadlock_detected THEN
                IF v_attempt >= p_max_retries THEN
                    RAISE;  -- 超過重試次數，拋出原始錯誤
                END IF;
                RAISE NOTICE '第 % 次嘗試失敗，重試中...', v_attempt;
                PERFORM pg_sleep(0.1 * v_attempt);  -- 指數退避
        END;
    END LOOP;

    RETURN v_result;
END;
$$;

-- PL/pgSQL 的 SELECT INTO 是賦值
SELECT name INTO v_name FROM users WHERE id = 1;

-- SQL 標準的 SELECT INTO 是建表（不要在 PL/pgSQL 中使用）
-- SELECT name INTO new_table FROM users;  -- 語義不同！

-- 在 PL/pgSQL 中建表用 CREATE TABLE AS
CREATE TABLE new_table AS SELECT name FROM users;

-- 危險：v_id 同名於 users.id，WHERE 條件永遠為 true！
DECLARE
    id INT := 5;
BEGIN
    SELECT name INTO v_name FROM users WHERE id = id;
    -- PostgreSQL 會把兩個 id 都解讀為欄位 → WHERE users.id = users.id → 永遠 true

    -- 解法 1：變數加 prefix
    -- DECLARE v_id INT := 5;
    -- WHERE id = v_id;

    -- 解法 2：用 label 限定
    -- <<fn>> DECLARE id INT := 5;
    -- WHERE users.id = fn.id;
END;

-- 每次迭代都建立/釋放 SAVEPOINT，效能極差
FOR v_rec IN SELECT * FROM big_table LOOP
    BEGIN
        PERFORM risky_operation(v_rec.id);
    EXCEPTION
        WHEN OTHERS THEN NULL;  -- 吞掉錯誤
    END;
END LOOP;

-- 改進：批次處理，只在外層捕獲
BEGIN
    FOR v_rec IN SELECT * FROM big_table LOOP
        PERFORM risky_operation(v_rec.id);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        -- 整批回滾，記錄錯誤
        RAISE WARNING 'Batch failed: %', SQLERRM;
END;

-- 錯誤：NULL 比較結果永遠是 NULL（不是 TRUE 也不是 FALSE）
IF v_name = NULL THEN ...     -- 永遠不會進入
IF v_name != 'test' THEN ...  -- v_name 為 NULL 時也不會進入

-- 正確做法
IF v_name IS NULL THEN ...
IF v_name IS DISTINCT FROM 'test' THEN ...  -- NULL-safe 比較