PostgreSQL 存储过程与函数

一、PL/pgSQL 基础

1.1 PL/pgSQL 概述

PL/pgSQL（Procedural Language/PostgreSQL）是 PostgreSQL 的过程化编程语言，用于编写存储过程、函数和触发器。

1.2 基本语法结构

-- 函数基本结构
CREATE [OR REPLACE] FUNCTION function_name(
    param1 data_type,
    param2 data_type DEFAULT default_value
)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
    variable_name data_type;
BEGIN
    -- 函数体
    RETURN result;
END;
$$;

-- 存储过程（PostgreSQL 11+）
CREATE [OR REPLACE] PROCEDURE procedure_name(
    param1 data_type,
    INOUT param2 data_type
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 过程体
END;
$$;

-- 调用函数
SELECT function_name(arg1, arg2);

-- 调用存储过程
CALL procedure_name(arg1, arg2);

1.3 变量与数据类型

-- 变量声明
CREATE OR REPLACE FUNCTION variable_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    -- 基本类型
    v_int INTEGER := 0;
    v_text TEXT := 'hello';
    v_bool BOOLEAN := TRUE;
    v_numeric NUMERIC(10, 2);
    v_date DATE := CURRENT_DATE;
    v_timestamp TIMESTAMPTZ := NOW();

    -- 引用表列类型
    v_user_name users.name%TYPE;

    -- 引用表行类型
    v_user_row users%ROWTYPE;

    -- 记录类型（动态）
    v_record RECORD;

    -- 数组
    v_arr INTEGER[] := ARRAY[1, 2, 3];

    -- 常量
    c_pi CONSTANT NUMERIC := 3.14159;

    -- NOT NULL 约束
    v_required TEXT NOT NULL := 'must have value';
BEGIN
    -- 赋值
    v_int := 100;
    v_text := 'world';

    -- 查询赋值
    SELECT name INTO v_user_name FROM users WHERE id = 1;

    -- 行赋值
    SELECT * INTO v_user_row FROM users WHERE id = 1;
    RAISE NOTICE 'User: %, Email: %', v_user_row.name, v_user_row.email;

    -- 数组操作
    v_arr := array_append(v_arr, 4);
    RAISE NOTICE 'Array: %', v_arr;
END;
$$;

二、函数

2.1 创建函数

-- 简单函数
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

SELECT add_numbers(1, 2);  -- 返回 3

-- 带默认参数
CREATE OR REPLACE FUNCTION greet(name TEXT DEFAULT 'World')
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN 'Hello, ' || name || '!';
END;
$$;

SELECT greet();        -- Hello, World!
SELECT greet('John'); -- Hello, John!

-- 带 OUT 参数
CREATE OR REPLACE FUNCTION get_user_info(
    p_user_id INTEGER,
    OUT o_name TEXT,
    OUT o_email TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT name, email INTO o_name, o_email
    FROM users WHERE id = p_user_id;
END;
$$;

SELECT * FROM get_user_info(1);

-- 返回表
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE (
    user_id INTEGER,
    user_name TEXT,
    user_email TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT id, name, email FROM users WHERE status = 1;
END;
$$;

SELECT * FROM get_active_users();

-- 返回 SETOF
CREATE OR REPLACE FUNCTION get_users_by_status(p_status INTEGER)
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT * FROM users WHERE status = p_status;
END;
$$;

SELECT * FROM get_users_by_status(1);

2.2 SQL 函数

-- 纯 SQL 函数（更简洁，可能更快）
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS BIGINT
LANGUAGE SQL
AS $$
    SELECT COUNT(*) FROM users;
$$;

-- 内联 SQL 函数
CREATE OR REPLACE FUNCTION get_user_by_id(p_id INTEGER)
RETURNS users
LANGUAGE SQL
AS $$
    SELECT * FROM users WHERE id = p_id;
$$;

-- 带参数的 SQL 函数
CREATE OR REPLACE FUNCTION calc_discount(price NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE  -- 相同输入总是相同输出
AS $$
    SELECT price * (1 - rate);
$$;

2.3 函数属性

-- 函数属性
CREATE OR REPLACE FUNCTION my_function(...)
RETURNS ...
LANGUAGE plpgsql
VOLATILE           -- 可能修改数据库（默认）
-- STABLE          -- 不修改数据库，相同事务内相同输入相同输出
-- IMMUTABLE       -- 不修改数据库，永远相同输入相同输出
STRICT             -- 任一参数为 NULL 则返回 NULL
-- RETURNS NULL ON NULL INPUT  -- 同 STRICT
-- CALLED ON NULL INPUT        -- 即使参数为 NULL 也调用（默认）
SECURITY INVOKER   -- 以调用者权限执行（默认）
-- SECURITY DEFINER -- 以定义者权限执行
PARALLEL SAFE      -- 可以并行执行
-- PARALLEL RESTRICTED  -- 只能在并行组的主进程执行
-- PARALLEL UNSAFE      -- 不能并行执行（默认）
COST 100           -- 执行成本估算（用于优化器）
ROWS 1000          -- 返回行数估算
AS $$
BEGIN
    -- 函数体
END;
$$;

-- 示例：不可变函数（优化器可以缓存结果）
CREATE OR REPLACE FUNCTION full_name(first_name TEXT, last_name TEXT)
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT first_name || ' ' || last_name;
$$;

-- 示例：SECURITY DEFINER（提权）
CREATE OR REPLACE FUNCTION safe_delete_user(p_user_id INTEGER)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER  -- 以函数所有者权限执行
SET search_path = public  -- 防止搜索路径攻击
AS $$
BEGIN
    -- 检查权限
    IF NOT current_user_has_permission('delete_user') THEN
        RAISE EXCEPTION 'Permission denied';
    END IF;

    DELETE FROM users WHERE id = p_user_id;
END;
$$;

三、存储过程

3.1 存储过程基础

-- 存储过程（PostgreSQL 11+）
-- 与函数的区别：可以有事务控制，不返回值

CREATE OR REPLACE PROCEDURE transfer_funds(
    sender_id INTEGER,
    receiver_id INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 扣款
    UPDATE accounts SET balance = balance - amount
    WHERE id = sender_id;

    -- 检查余额
    IF NOT FOUND OR (SELECT balance FROM accounts WHERE id = sender_id) < 0 THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- 存款
    UPDATE accounts SET balance = balance + amount
    WHERE id = receiver_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Receiver account not found';
    END IF;

    -- 记录交易
    INSERT INTO transactions (sender_id, receiver_id, amount, created_at)
    VALUES (sender_id, receiver_id, amount, NOW());
END;
$$;

-- 调用存储过程
CALL transfer_funds(1, 2, 100.00);

3.2 事务控制

-- 存储过程中的事务控制
CREATE OR REPLACE PROCEDURE batch_process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    v_order RECORD;
    v_count INTEGER := 0;
BEGIN
    FOR v_order IN SELECT * FROM orders WHERE status = 'pending' LOOP
        BEGIN
            -- 处理订单
            UPDATE orders SET status = 'processing' WHERE id = v_order.id;

            -- 每处理 100 个订单提交一次
            v_count := v_count + 1;
            IF v_count % 100 = 0 THEN
                COMMIT;  -- 提交当前事务
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                -- 单个订单失败，回滚并继续
                ROLLBACK;
                INSERT INTO error_logs (order_id, error_message)
                VALUES (v_order.id, SQLERRM);
        END;
    END LOOP;

    -- 提交剩余的
    COMMIT;
END;
$$;

-- INOUT 参数
CREATE OR REPLACE PROCEDURE increment_counter(INOUT counter INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    counter := counter + 1;
END;
$$;

DO $$
DECLARE
    v_counter INTEGER := 0;
BEGIN
    CALL increment_counter(v_counter);
    RAISE NOTICE 'Counter: %', v_counter;  -- 1
END;
$$;

四、控制结构

4.1 条件语句

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

-- CASE 表达式
CREATE OR REPLACE FUNCTION get_status_text(status INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE status
        WHEN 0 THEN '禁用'
        WHEN 1 THEN '启用'
        WHEN 2 THEN '待审核'
        ELSE '未知'
    END;
END;
$$;

-- 搜索 CASE
CREATE OR REPLACE FUNCTION categorize_amount(amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE
        WHEN amount >= 10000 THEN '大额'
        WHEN amount >= 1000 THEN '中额'
        WHEN amount > 0 THEN '小额'
        ELSE '无效'
    END;
END;
$$;

4.2 循环语句

-- LOOP 循环
CREATE OR REPLACE FUNCTION loop_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 1;
BEGIN
    LOOP
        RAISE NOTICE 'i = %', i;
        i := i + 1;
        EXIT WHEN i > 5;  -- 退出条件
    END LOOP;
END;
$$;

-- WHILE 循环
CREATE OR REPLACE FUNCTION while_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    i INTEGER := 1;
BEGIN
    WHILE i <= 5 LOOP
        RAISE NOTICE 'i = %', i;
        i := i + 1;
    END LOOP;
END;
$$;

-- FOR 循环（数字）
CREATE OR REPLACE FUNCTION for_number_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;

    -- 反向
    FOR i IN REVERSE 5..1 LOOP
        RAISE NOTICE 'reverse i = %', i;
    END LOOP;

    -- 步长
    FOR i IN 1..10 BY 2 LOOP
        RAISE NOTICE 'step i = %', i;
    END LOOP;
END;
$$;

-- FOR 循环（查询结果）
CREATE OR REPLACE FUNCTION for_query_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_user RECORD;
BEGIN
    FOR v_user IN SELECT id, name FROM users WHERE status = 1 LOOP
        RAISE NOTICE 'User: % - %', v_user.id, v_user.name;
    END LOOP;
END;
$$;

-- FOREACH 循环（数组）
CREATE OR REPLACE FUNCTION foreach_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_arr INTEGER[] := ARRAY[1, 2, 3, 4, 5];
    v_elem INTEGER;
BEGIN
    FOREACH v_elem IN ARRAY v_arr LOOP
        RAISE NOTICE 'element = %', v_elem;
    END LOOP;
END;
$$;

-- CONTINUE 和 EXIT
CREATE OR REPLACE FUNCTION control_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..10 LOOP
        IF i = 3 THEN
            CONTINUE;  -- 跳过当前迭代
        END IF;

        IF i = 8 THEN
            EXIT;  -- 退出循环
        END IF;

        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

4.3 动态 SQL

-- EXECUTE 执行动态 SQL
CREATE OR REPLACE FUNCTION dynamic_query(table_name TEXT, column_name TEXT, value TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE
        format('SELECT * FROM %I WHERE %I = $1', table_name, column_name)
        USING value;
END;
$$;

-- 带变量的动态 SQL
CREATE OR REPLACE FUNCTION search_users(
    p_name TEXT DEFAULT NULL,
    p_email TEXT DEFAULT NULL,
    p_status INTEGER DEFAULT NULL
)
RETURNS SETOF users
LANGUAGE plpgsql
AS $$
DECLARE
    v_sql TEXT := 'SELECT * FROM users WHERE 1=1';
BEGIN
    IF p_name IS NOT NULL THEN
        v_sql := v_sql || ' AND name ILIKE ' || quote_literal('%' || p_name || '%');
    END IF;

    IF p_email IS NOT NULL THEN
        v_sql := v_sql || ' AND email = ' || quote_literal(p_email);
    END IF;

    IF p_status IS NOT NULL THEN
        v_sql := v_sql || ' AND status = ' || p_status;
    END IF;

    RETURN QUERY EXECUTE v_sql;
END;
$$;

-- 使用 format 函数（更安全）
CREATE OR REPLACE FUNCTION get_table_count(table_name TEXT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    v_count BIGINT;
BEGIN
    EXECUTE format('SELECT COUNT(*) FROM %I', table_name) INTO v_count;
    RETURN v_count;
END;
$$;

五、异常处理

5.1 异常捕获

CREATE OR REPLACE FUNCTION exception_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    -- 可能引发异常的代码
    SELECT id INTO STRICT v_user_id FROM users WHERE email = 'notexist@example.com';

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'User not found';

    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'Multiple users found';

    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %, State: %', SQLERRM, SQLSTATE;
END;
$$;

-- 常用异常条件
-- NO_DATA_FOUND: 查询没有返回数据
-- TOO_MANY_ROWS: 返回多行（INTO STRICT 时）
-- UNIQUE_VIOLATION: 唯一约束违反
-- FOREIGN_KEY_VIOLATION: 外键约束违反
-- CHECK_VIOLATION: CHECK 约束违反
-- NOT_NULL_VIOLATION: NOT NULL 约束违反
-- DIVISION_BY_ZERO: 除以零
-- OTHERS: 所有其他异常

5.2 抛出异常

CREATE OR REPLACE FUNCTION validate_and_create_user(
    p_name TEXT,
    p_email TEXT
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INTEGER;
BEGIN
    -- 验证名称
    IF p_name IS NULL OR LENGTH(TRIM(p_name)) < 2 THEN
        RAISE EXCEPTION 'Invalid name: must be at least 2 characters'
            USING ERRCODE = 'check_violation';
    END IF;

    -- 验证邮箱
    IF p_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', p_email
            USING ERRCODE = 'check_violation',
                  HINT = 'Please provide a valid email address';
    END IF;

    -- 检查邮箱是否已存在
    IF EXISTS (SELECT 1 FROM users WHERE email = p_email) THEN
        RAISE EXCEPTION 'Email already exists'
            USING ERRCODE = 'unique_violation',
                  DETAIL = format('Email %s is already registered', p_email);
    END IF;

    -- 创建用户
    INSERT INTO users (name, email) VALUES (p_name, p_email)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;
END;
$$;

-- RAISE 级别
-- DEBUG, LOG, INFO, NOTICE, WARNING, EXCEPTION

CREATE OR REPLACE FUNCTION raise_levels_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE DEBUG 'Debug message';      -- 通常不显示
    RAISE LOG 'Log message';          -- 记录到日志
    RAISE INFO 'Info message';        -- 显示给用户
    RAISE NOTICE 'Notice message';    -- 显示给用户
    RAISE WARNING 'Warning message';  -- 警告
    RAISE EXCEPTION 'Error message';  -- 错误，终止执行
END;
$$;

5.3 获取诊断信息

CREATE OR REPLACE FUNCTION diagnostic_demo()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows_affected INTEGER;
    v_result_oid OID;
BEGIN
    UPDATE users SET status = 1 WHERE status = 0;

    -- 获取受影响的行数
    GET DIAGNOSTICS v_rows_affected = ROW_COUNT;
    RAISE NOTICE 'Rows updated: %', v_rows_affected;

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

            RAISE NOTICE 'SQLSTATE: %', v_sqlstate;
            RAISE NOTICE 'Message: %', v_message;
            RAISE NOTICE 'Detail: %', v_detail;
            RAISE NOTICE 'Hint: %', v_hint;
            RAISE NOTICE 'Context: %', v_context;

            RAISE;  -- 重新抛出异常
        END;
END;
$$;

六、触发器

6.1 触发器基础

-- 触发器函数（必须返回 TRIGGER）
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

-- 创建触发器
CREATE TRIGGER trigger_users_updated
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

-- 触发器类型
-- BEFORE: 操作前触发
-- AFTER: 操作后触发
-- INSTEAD OF: 替代操作（视图）

-- 触发级别
-- FOR EACH ROW: 每行触发
-- FOR EACH STATEMENT: 每语句触发

-- 触发事件
-- INSERT, UPDATE, DELETE, TRUNCATE

6.2 触发器实例

-- 1. 审计日志触发器
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_by TEXT,
    changed_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs (table_name, operation, new_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW), current_user);
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs (table_name, operation, old_data, new_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW), current_user);
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs (table_name, operation, old_data, changed_by)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), current_user);
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER users_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- 2. 数据验证触发器
CREATE OR REPLACE FUNCTION validate_user_data()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 验证邮箱格式
    IF NEW.email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format: %', NEW.email;
    END IF;

    -- 验证名称长度
    IF LENGTH(NEW.name) < 2 THEN
        RAISE EXCEPTION 'Name must be at least 2 characters';
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER users_validate_trigger
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION validate_user_data();

-- 3. 自动填充触发器
CREATE OR REPLACE FUNCTION auto_fill_fields()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- 插入时设置创建时间
    IF TG_OP = 'INSERT' THEN
        NEW.created_at := COALESCE(NEW.created_at, NOW());
        NEW.created_by := COALESCE(NEW.created_by, current_user);
    END IF;

    -- 更新时设置修改时间
    NEW.updated_at := NOW();
    NEW.updated_by := current_user;

    RETURN NEW;
END;
$$;

-- 4. 级联更新触发器
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        UPDATE orders
        SET total_amount = (
            SELECT COALESCE(SUM(quantity * unit_price), 0)
            FROM order_items
            WHERE order_id = NEW.order_id
        )
        WHERE id = NEW.order_id;
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        UPDATE orders
        SET total_amount = (
            SELECT COALESCE(SUM(quantity * unit_price), 0)
            FROM order_items
            WHERE order_id = OLD.order_id
        )
        WHERE id = OLD.order_id;
        RETURN OLD;
    END IF;
END;
$$;

CREATE TRIGGER order_items_update_total
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW EXECUTE FUNCTION update_order_total();

6.3 触发器管理

-- 查看触发器
SELECT
    trigger_name,
    event_manipulation,
    event_object_table,
    action_timing,
    action_statement
FROM information_schema.triggers
WHERE trigger_schema = 'public';

-- 禁用触发器
ALTER TABLE users DISABLE TRIGGER users_audit_trigger;
ALTER TABLE users DISABLE TRIGGER ALL;  -- 禁用所有

-- 启用触发器
ALTER TABLE users ENABLE TRIGGER users_audit_trigger;
ALTER TABLE users ENABLE TRIGGER ALL;

-- 删除触发器
DROP TRIGGER IF EXISTS users_audit_trigger ON users;

-- 触发器变量
-- TG_NAME: 触发器名称
-- TG_WHEN: BEFORE, AFTER, INSTEAD OF
-- TG_LEVEL: ROW, STATEMENT
-- TG_OP: INSERT, UPDATE, DELETE, TRUNCATE
-- TG_TABLE_NAME: 表名
-- TG_TABLE_SCHEMA: Schema 名
-- NEW: 新行（INSERT/UPDATE）
-- OLD: 旧行（UPDATE/DELETE）

七、实战案例

7.1 订单处理系统

-- 创建订单并处理库存
CREATE OR REPLACE FUNCTION create_order(
    p_user_id INTEGER,
    p_items JSONB  -- [{"product_id": 1, "quantity": 2}, ...]
)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INTEGER;
    v_item JSONB;
    v_product_id INTEGER;
    v_quantity INTEGER;
    v_price NUMERIC;
    v_stock INTEGER;
BEGIN
    -- 创建订单
    INSERT INTO orders (user_id, status, created_at)
    VALUES (p_user_id, 'pending', NOW())
    RETURNING id INTO v_order_id;

    -- 处理订单项
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        v_product_id := (v_item->>'product_id')::INTEGER;
        v_quantity := (v_item->>'quantity')::INTEGER;

        -- 获取产品信息并锁定
        SELECT price, stock INTO v_price, v_stock
        FROM products
        WHERE id = v_product_id
        FOR UPDATE;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Product not found: %', v_product_id;
        END IF;

        -- 检查库存
        IF v_stock < v_quantity THEN
            RAISE EXCEPTION 'Insufficient stock for product %: available %, requested %',
                v_product_id, v_stock, v_quantity;
        END IF;

        -- 扣减库存
        UPDATE products SET stock = stock - v_quantity
        WHERE id = v_product_id;

        -- 添加订单项
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (v_order_id, v_product_id, v_quantity, v_price);
    END LOOP;

    -- 计算订单总金额（由触发器自动完成）

    RETURN v_order_id;

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Failed to create order: %', SQLERRM;
END;
$$;

-- 使用示例
SELECT create_order(1, '[{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]'::JSONB);

7.2 权限检查函数

-- 行级安全策略辅助函数
CREATE OR REPLACE FUNCTION current_user_id()
RETURNS INTEGER
LANGUAGE SQL
STABLE
AS $$
    SELECT NULLIF(current_setting('app.current_user_id', true), '')::INTEGER;
$$;

-- 检查用户是否有权限访问数据
CREATE OR REPLACE FUNCTION can_access_resource(
    p_resource_type TEXT,
    p_resource_id INTEGER,
    p_permission TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_user_id INTEGER := current_user_id();
    v_has_permission BOOLEAN;
BEGIN
    IF v_user_id IS NULL THEN
        RETURN FALSE;
    END IF;

    -- 检查直接权限
    SELECT EXISTS (
        SELECT 1 FROM user_permissions
        WHERE user_id = v_user_id
        AND resource_type = p_resource_type
        AND (resource_id = p_resource_id OR resource_id IS NULL)
        AND permission = p_permission
    ) INTO v_has_permission;

    IF v_has_permission THEN
        RETURN TRUE;
    END IF;

    -- 检查角色权限
    SELECT EXISTS (
        SELECT 1 FROM role_permissions rp
        JOIN user_roles ur ON rp.role_id = ur.role_id
        WHERE ur.user_id = v_user_id
        AND rp.resource_type = p_resource_type
        AND (rp.resource_id = p_resource_id OR rp.resource_id IS NULL)
        AND rp.permission = p_permission
    ) INTO v_has_permission;

    RETURN v_has_permission;
END;
$$;

-- 使用行级安全
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY documents_access_policy ON documents
    USING (can_access_resource('document', id, 'read'));

CREATE POLICY documents_modify_policy ON documents
    FOR UPDATE
    USING (can_access_resource('document', id, 'write'));

7.3 批量数据处理

-- 批量更新用户积分
CREATE OR REPLACE PROCEDURE batch_update_user_points(
    p_batch_size INTEGER DEFAULT 1000
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_processed INTEGER := 0;
    v_total INTEGER;
BEGIN
    -- 获取待处理总数
    SELECT COUNT(*) INTO v_total
    FROM pending_point_updates
    WHERE processed = FALSE;

    RAISE NOTICE 'Total pending updates: %', v_total;

    -- 批量处理
    WHILE v_processed < v_total LOOP
        -- 处理一批
        WITH batch AS (
            SELECT id, user_id, points, reason
            FROM pending_point_updates
            WHERE processed = FALSE
            ORDER BY id
            LIMIT p_batch_size
            FOR UPDATE SKIP LOCKED
        ),
        updated AS (
            UPDATE users u
            SET points = u.points + b.points
            FROM batch b
            WHERE u.id = b.user_id
            RETURNING b.id
        )
        UPDATE pending_point_updates
        SET processed = TRUE, processed_at = NOW()
        WHERE id IN (SELECT id FROM updated);

        -- 获取处理数量
        GET DIAGNOSTICS v_processed = ROW_COUNT;

        RAISE NOTICE 'Processed % records', v_processed;

        -- 提交当前批次
        COMMIT;
    END LOOP;

    RAISE NOTICE 'Batch processing completed';
END;
$$;

八、总结

函数 vs 存储过程

特性	函数	存储过程
返回值	必须有	没有（可用 OUT/INOUT）
事务控制	不能 COMMIT/ROLLBACK	可以
调用方式	SELECT func()	CALL proc()
SQL 中使用	可以	不可以
触发器	可以	不可以

最佳实践

1. 命名规范：使用前缀区分（fn*、sp*、trg_）
2. 错误处理：总是使用异常处理
3. 事务边界：明确事务控制
4. 性能考虑：避免过多的小事务
5. 安全性：使用参数化查询，避免 SQL 注入
6. 可维护性：添加注释，保持函数简单
7. 版本控制：将函数定义纳入版本控制