存储过程与函数

CREATE TABLE recon_results (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id  bigint,
    diff_kind   text,          -- 'MISSING' | 'AMOUNT' | 'BATCH_ERROR'
    detail      text,
    batch_no    int
);

CREATE OR REPLACE PROCEDURE reconcile_batch(
    p_batch_size int DEFAULT 10000
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_batch   int := 0;
    v_done    bigint;
    r         record;          -- 行变量，循环里逐行取
BEGIN
    LOOP
        v_batch := v_batch + 1;
        v_done := 0;

        -- 每批一个独立事务：COMMIT 后行锁释放、WAL 刷盘
        BEGIN
            FOR r IN
                SELECT p.id, p.amount, b.bill_amount
                FROM payments p
                LEFT JOIN bills b ON b.payment_id = p.id
                WHERE p.recon_flag = false      -- 待对账标记
                ORDER BY p.id
                LIMIT p_batch_size
                FOR UPDATE SKIP LOCKED          -- 多实例并行跑互不抢行
            LOOP
                IF r.bill_amount IS NULL THEN
                    INSERT INTO recon_results (payment_id, diff_kind, detail, batch_no)
                    VALUES (r.id, 'MISSING', '无对应账单', v_batch);
                ELSIF r.bill_amount <> r.amount THEN
                    INSERT INTO recon_results (payment_id, diff_kind, detail, batch_no)
                    VALUES (r.id, 'AMOUNT',
                            format('流水 %s vs 账单 %s', r.amount, r.bill_amount),
                            v_batch);
                END IF;

                UPDATE payments SET recon_flag = true WHERE id = r.id;
                v_done := v_done + 1;
            END LOOP;

            COMMIT;   -- 过程里合法：本批提交，下一批新事务
        EXCEPTION
            WHEN OTHERS THEN
                -- 本批出错：记录后回滚，继续下一批（不让单条脏数据停整个对账）
                INSERT INTO recon_results (payment_id, diff_kind, detail, batch_no)
                VALUES (NULL, 'BATCH_ERROR', SQLERRM, v_batch);
                ROLLBACK;
        END;

        EXIT WHEN v_done < p_batch_size;   -- 没取满一批 = 干完了
    END LOOP;

    RAISE NOTICE '对账完成，共 % 批', v_batch;
END;
$$;

CALL reconcile_batch(50000);

CREATE OR REPLACE FUNCTION update_order_status(
    p_order_id bigint,
    p_new_status text
)
RETURNS TABLE (order_id bigint, status text, changed_at timestamptz)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 校验：状态机白名单
    IF p_new_status NOT IN ('paid', 'shipped', 'done', 'cancelled') THEN
        RAISE EXCEPTION '非法状态: %', p_new_status
            USING ERRCODE = 'check_violation';
    END IF;

    RETURN QUERY
    UPDATE orders o
    SET status = p_new_status,
        updated_at = now()
    WHERE o.id = p_order_id
    RETURNING o.id, o.status, o.updated_at;

    IF NOT FOUND THEN
        RAISE EXCEPTION '订单不存在: %', p_order_id;
    END IF;
END;
$$;

-- 调用：函数直接当表用
SELECT * FROM update_order_status(1001, 'shipped');

CREATE OR REPLACE FUNCTION reset_user_password(p_user_id bigint, p_hash text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER                        -- 以属主权限执行，绕过调用者的表权限
SET search_path = pg_catalog, public    -- 锁定 search_path，防劫持
AS $$
BEGIN
    UPDATE users SET password_hash = p_hash WHERE id = p_user_id;
END;
$$;

REVOKE ALL ON FUNCTION reset_user_password(bigint, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION reset_user_password(bigint, text) TO app_support;

CREATE OR REPLACE FUNCTION get_rate(p_date date) RETURNS numeric
LANGUAGE sql
STABLE            -- 只读：同一条语句内同参数返回不变（可下推/可用在索引条件）
PARALLEL SAFE     -- 可在并行 worker 里执行（默认 UNSAFE，强制串行）
COST 10           -- 代价提示，影响优化器排序
AS $$ SELECT rate FROM price_windows WHERE effective @> p_date $$;

CREATE OR REPLACE PROCEDURE process_orders() AS $$
DECLARE
    order_record RECORD;
BEGIN
    FOR order_record IN
        SELECT id, amount FROM orders WHERE status = 'pending'
    LOOP
        UPDATE orders SET status = 'processing' WHERE id = order_record.id;
        -- 处理逻辑
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EXTENSION plpython3u;

CREATE OR REPLACE FUNCTION python_hash(p_text TEXT)
RETURNS TEXT AS $$
import hashlib
return hashlib.sha256(p_text.encode()).hexdigest()
$$ LANGUAGE plpython3u;

CREATE EXTENSION plperl;

CREATE OR REPLACE FUNCTION perl_reverse(p_text TEXT)
RETURNS TEXT AS $$
return reverse($_[0]);
$$ LANGUAGE plperl;