PL/pgSQL 存储过程与触发器：函数、事务控制与性能陷阱

-- 函数：返回标量，可在 SELECT 中调用
CREATE FUNCTION add_numbers(a int, b int)
RETURNS int AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 存储过程：无返回值，可 COMMIT
CREATE PROCEDURE batch_archive(days int) AS $$
BEGIN
    INSERT INTO archive SELECT * FROM events WHERE age > days;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT add_numbers(1, 2);        -- 3
CALL batch_archive(30);          -- 过程用 CALL

CREATE OR REPLACE FUNCTION process_order(order_id bigint)
RETURNS numeric AS $$
DECLARE
    total numeric := 0;
    item_count int;
    row RECORD;
BEGIN
    -- 变量赋值（用 := 或 INTO）
    SELECT count(*) INTO item_count FROM order_items WHERE oid = order_id;
    IF item_count = 0 THEN
        RAISE EXCEPTION '订单 % 无明细', order_id;
    END IF;

    -- 循环
    FOR row IN SELECT price, qty FROM order_items WHERE oid = order_id LOOP
        total := total + row.price * row.qty;
    END LOOP;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION list_orders(cid bigint) RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY SELECT * FROM orders WHERE customer_id = cid;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION safe_insert(payload jsonb) RETURNS text AS $$
BEGIN
    BEGIN
        INSERT INTO ledger(data) VALUES (payload);
        RETURN 'ok';
    EXCEPTION
        WHEN unique_violation THEN
            RETURN 'duplicate';       -- 捕获唯一冲突
        WHEN OTHERS THEN
            RETURN 'error: ' || SQLERRM;
    END;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE process_batch(n int) AS $$
BEGIN
    FOR i IN 1..n LOOP
        INSERT INTO jobs(id) VALUES (i);
        IF i % 100 = 0 THEN
            COMMIT;      -- 每 100 条提交一次，防长事务膨胀
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 创建触发函数
CREATE FUNCTION audit_order_changes() RETURNS trigger AS $$
BEGIN
    INSERT INTO order_audit(oid, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trg_order_audit
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_order_changes();

-- 语句级：整批只触发一次，性能更好
CREATE TRIGGER trg_stat_audit
AFTER UPDATE ON orders
FOR EACH STATEMENT
EXECUTE FUNCTION audit_batch();

CREATE FUNCTION check_order_total() RETURNS trigger AS $$
BEGIN
    IF NEW.amount <= 0 THEN
        RAISE EXCEPTION '订单金额必须为正';
    END IF;
    -- 服务端强制，绕过应用也不破
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_total BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION check_order_total();

CREATE FUNCTION audit_users() RETURNS trigger AS $$
BEGIN
    IF (TG_OP = 'UPDATE' AND OLD IS DISTINCT FROM NEW) OR TG_OP = 'DELETE' THEN
        INSERT INTO user_audit(uid, action, old_json, new_json, who, at)
        VALUES (COALESCE(NEW.id, OLD.id), TG_OP,
                to_jsonb(OLD), to_jsonb(NEW),
                current_user, now());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION touch_updated() RETURNS trigger AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION find_orders(cid bigint, lim int) RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY SELECT * FROM orders
        WHERE customer_id = cid LIMIT lim;   -- 计划按首次调用参数缓存
END;
$$ LANGUAGE plpgsql;

-- 危险：A 表 UPDATE 触发器更新 B 表，B 表触发器又更新 A 表
CREATE FUNCTION a_trg() RETURNS trigger AS $$
BEGIN
    UPDATE b SET a_id = NEW.id WHERE a_id = OLD.id;   -- 触发 B 的触发器
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 会话变量标记法
CREATE FUNCTION safe_a_trg() RETURNS trigger AS $$
BEGIN
    IF current_setting('myapp.in_a_trg', true) = 'on' THEN
        RETURN NEW;
    END IF;
    PERFORM set_config('myapp.in_a_trg', 'on', false);
    UPDATE b SET a_id = NEW.id WHERE a_id = OLD.id;
    PERFORM set_config('myapp.in_a_trg', 'off', false);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;