PostgreSQL教程

存储过程与函数的区别

基本概念

PostgreSQL中存储过程和函数的主要区别：

创建函数

基本语法

创建函数的基本语法结构：

CREATE [OR REPLACE] FUNCTION function_name (
    [parameter_name parameter_type [DEFAULT default_value]] [, ...]
)
RETURNS return_type AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 函数体
    RETURN return_value;
EXCEPTION
    -- 异常处理
END;
$$ LANGUAGE plpgsql;

简单函数示例

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

-- 调用函数
SELECT add_numbers(5, 3); -- 返回 8

带默认参数的函数

-- 创建带默认参数的函数
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
)
RETURNS NUMERIC(10,2) AS $$
BEGIN
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT calculate_discount(100.00); -- 使用默认折扣10%，返回 90.00
SELECT calculate_discount(100.00, 20.00); -- 指定折扣20%，返回 80.00

返回表的函数

-- 创建返回表的函数
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(
    user_id INTEGER,
    username VARCHAR(50),
    email VARCHAR(255)
) AS $$
BEGIN
    RETURN QUERY
    SELECT id, username, email
    FROM users
    WHERE is_active = true;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_active_users();

返回集合的函数

-- 创建返回集合的函数
CREATE OR REPLACE FUNCTION get_user_orders(user_id INTEGER)
RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM orders WHERE orders.user_id = get_user_orders.user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM get_user_orders(1);

创建存储过程

基本语法

创建存储过程的基本语法（PostgreSQL 11+）：

CREATE [OR REPLACE] PROCEDURE procedure_name (
    [parameter_name parameter_mode parameter_type [DEFAULT default_value]] [, ...]
)
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
BEGIN
    -- 存储过程体
END;
$$;

简单存储过程示例

-- 创建简单的存储过程
CREATE OR REPLACE PROCEDURE create_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_first_name VARCHAR(50),
    p_last_name VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users (username, email, first_name, last_name, created_at)
    VALUES (p_username, p_email, p_first_name, p_last_name, CURRENT_TIMESTAMP);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 调用存储过程
CALL create_user('newuser', 'newuser@example.com', 'New', 'User');

带INOUT参数的存储过程

-- 创建带INOUT参数的存储过程
CREATE OR REPLACE PROCEDURE update_user_stats(
    p_user_id INTEGER,
    INOUT p_order_count INTEGER DEFAULT 0,
    INOUT p_total_spent NUMERIC(12,2) DEFAULT 0.00
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE user_id = p_user_id;
END;
$$;

-- 调用存储过程
CALL update_user_stats(1, 0, 0.00);

参数模式

参数类型

PostgreSQL支持的参数模式：

-- IN参数（默认）- 输入参数
CREATE OR REPLACE FUNCTION greet_user(name VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

-- OUT参数 - 输出参数
CREATE OR REPLACE FUNCTION get_user_info(user_id INTEGER, OUT username VARCHAR, OUT email VARCHAR)
AS $$
BEGIN
    SELECT u.username, u.email INTO get_user_info.username, get_user_info.email
    FROM users u
    WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

-- INOUT参数 - 输入输出参数
CREATE OR REPLACE FUNCTION swap_values(INOUT a INTEGER, INOUT b INTEGER)
AS $$
DECLARE
    temp INTEGER;
BEGIN
    temp := a;
    a := b;
    b := temp;
END;
$$ LANGUAGE plpgsql;

可变参数函数

-- 创建可变参数函数
CREATE OR REPLACE FUNCTION sum_numbers(VARIADIC numbers INTEGER[])
RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    num INTEGER;
BEGIN
    FOREACH num IN ARRAY numbers LOOP
        total := total + num;
    END LOOP;
    
    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- 调用可变参数函数
SELECT sum_numbers(1, 2, 3, 4, 5); -- 返回 15
SELECT sum_numbers(10, 20, 30); -- 返回 60

复杂示例

业务逻辑函数

-- 创建订单处理函数
CREATE OR REPLACE FUNCTION process_order(
    p_user_id INTEGER,
    p_products JSONB
)
RETURNS INTEGER AS $$
DECLARE
    order_id INTEGER;
    product_item JSONB;
    product_id INTEGER;
    quantity INTEGER;
    price NUMERIC(10,2);
    total_amount NUMERIC(10,2) := 0;
BEGIN
    -- 开始事务
    BEGIN
        -- 创建订单
        INSERT INTO orders (user_id, total_amount, status, created_at)
        VALUES (p_user_id, 0, 'pending', CURRENT_TIMESTAMP)
        RETURNING id INTO order_id;
        
        -- 处理每个产品
        FOR product_item IN SELECT jsonb_array_elements(p_products)
        LOOP
            product_id := (product_item->>'product_id')::INTEGER;
            quantity := (product_item->>'quantity')::INTEGER;
            
            -- 获取产品价格
            SELECT price INTO price FROM products WHERE id = product_id;
            
            -- 检查库存
            IF (SELECT stock_quantity FROM products WHERE id = product_id) < quantity THEN
                RAISE EXCEPTION 'Insufficient stock for product %', product_id;
            END IF;
            
            -- 添加订单项
            INSERT INTO order_items (order_id, product_id, quantity, price)
            VALUES (order_id, product_id, quantity, price);
            
            -- 更新库存
            UPDATE products 
            SET stock_quantity = stock_quantity - quantity 
            WHERE id = product_id;
            
            -- 计算总金额
            total_amount := total_amount + (price * quantity);
        END LOOP;
        
        -- 更新订单总金额
        UPDATE orders 
        SET total_amount = total_amount 
        WHERE id = order_id;
        
        -- 记录日志
        INSERT INTO order_logs (order_id, action, details, created_at)
        VALUES (order_id, 'created', 'Order processed successfully', CURRENT_TIMESTAMP);
        
        -- 提交事务
        RETURN order_id;
    EXCEPTION
        WHEN OTHERS THEN
            -- 回滚事务
            RAISE NOTICE 'Order processing failed: %', SQLERRM;
            RAISE;
    END;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT process_order(1, '[{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]');

数据清洗存储过程

-- 创建数据清洗存储过程
CREATE OR REPLACE PROCEDURE clean_user_data()
LANGUAGE plpgsql
AS $$
DECLARE
    cleaned_count INTEGER := 0;
BEGIN
    -- 清洗空用户名
    UPDATE users 
    SET username = 'anonymous_' || id 
    WHERE username IS NULL OR TRIM(username) = '';
    GET DIAGNOSTICS cleaned_count = ROW_COUNT;
    RAISE NOTICE 'Cleaned % users with empty usernames', cleaned_count;
    
    -- 清洗无效邮箱
    UPDATE users 
    SET email = NULL 
    WHERE email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
    GET DIAGNOSTICS cleaned_count = ROW_COUNT;
    RAISE NOTICE 'Cleaned % users with invalid emails', cleaned_count;
    
    -- 删除重复用户
    DELETE FROM users 
    WHERE id IN (
        SELECT id FROM (
            SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn
            FROM users 
            WHERE email IS NOT NULL
        ) t WHERE rn > 1
    );
    GET DIAGNOSTICS cleaned_count = ROW_COUNT;
    RAISE NOTICE 'Removed % duplicate users', cleaned_count;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Data cleaning failed: %', SQLERRM;
        RAISE;
END;
$$;

-- 调用存储过程
CALL clean_user_data();

最佳实践

命名规范

• 函数命名：使用动词开头，如get_user_info、calculate_total
• 存储过程命名：使用动词开头，如create_user、update_order
• 参数命名：使用p_前缀，如p_user_id、p_email
• 局部变量命名：使用有意义的名称，避免单字母变量

错误处理

-- 良好的错误处理示例
CREATE OR REPLACE FUNCTION safe_divide(dividend NUMERIC, divisor NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    result NUMERIC;
BEGIN
    -- 输入验证
    IF divisor = 0 THEN
        RAISE EXCEPTION 'Division by zero error' USING ERRCODE = '22012';
    END IF;
    
    -- 执行计算
    result := dividend / divisor;
    
    -- 返回结果
    RETURN result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide by zero';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE LOG 'Error in safe_divide: %', SQLERRM;
        RAISE EXCEPTION 'Calculation failed: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

性能优化

• 避免在循环中执行SQL：尽量使用集合操作
• 合理使用游标：只在必要时使用
• 索引优化：确保函数中使用的查询有适当索引
• 事务管理：在存储过程中合理使用事务控制

-- 创建完整的用户管理系统函数
CREATE OR REPLACE FUNCTION manage_user(
    p_action VARCHAR(20), -- 'CREATE', 'UPDATE', 'DELETE'
    p_user_id INTEGER DEFAULT NULL,
    p_username VARCHAR(50) DEFAULT NULL,
    p_email VARCHAR(255) DEFAULT NULL,
    p_first_name VARCHAR(50) DEFAULT NULL,
    p_last_name VARCHAR(50) DEFAULT NULL
)
RETURNS TABLE(
    result_code INTEGER,
    result_message VARCHAR(255),
    user_id INTEGER
) AS $$
DECLARE
    v_user_id INTEGER;
    v_action_count INTEGER;
BEGIN
    CASE UPPER(p_action)
        WHEN 'CREATE' THEN
            -- 验证输入
            IF p_username IS NULL OR p_email IS NULL THEN
                RETURN QUERY SELECT -1, 'Username and email are required for creation'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 检查用户是否已存在
            SELECT COUNT(*) INTO v_action_count 
            FROM users 
            WHERE username = p_username OR email = p_email;
            
            IF v_action_count > 0 THEN
                RETURN QUERY SELECT -2, 'User already exists'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 创建用户
            INSERT INTO users (username, email, first_name, last_name, created_at)
            VALUES (p_username, p_email, p_first_name, p_last_name, CURRENT_TIMESTAMP)
            RETURNING id INTO v_user_id;
            
            RETURN QUERY SELECT 0, 'User created successfully'::VARCHAR(255), v_user_id;
            
        WHEN 'UPDATE' THEN
            -- 验证用户ID
            IF p_user_id IS NULL THEN
                RETURN QUERY SELECT -3, 'User ID is required for update'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 检查用户是否存在
            SELECT COUNT(*) INTO v_action_count 
            FROM users 
            WHERE id = p_user_id;
            
            IF v_action_count = 0 THEN
                RETURN QUERY SELECT -4, 'User not found'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 更新用户
            UPDATE users 
            SET 
                username = COALESCE(p_username, username),
                email = COALESCE(p_email, email),
                first_name = COALESCE(p_first_name, first_name),
                last_name = COALESCE(p_last_name, last_name),
                updated_at = CURRENT_TIMESTAMP
            WHERE id = p_user_id;
            
            RETURN QUERY SELECT 0, 'User updated successfully'::VARCHAR(255), p_user_id;
            
        WHEN 'DELETE' THEN
            -- 验证用户ID
            IF p_user_id IS NULL THEN
                RETURN QUERY SELECT -3, 'User ID is required for deletion'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 检查用户是否存在
            SELECT COUNT(*) INTO v_action_count 
            FROM users 
            WHERE id = p_user_id;
            
            IF v_action_count = 0 THEN
                RETURN QUERY SELECT -4, 'User not found'::VARCHAR(255), NULL::INTEGER;
                RETURN;
            END IF;
            
            -- 删除用户
            DELETE FROM users WHERE id = p_user_id;
            
            RETURN QUERY SELECT 0, 'User deleted successfully'::VARCHAR(255), p_user_id;
            
        ELSE
            RETURN QUERY SELECT -5, 'Invalid action. Use CREATE, UPDATE, or DELETE'::VARCHAR(255), NULL::INTEGER;
    END CASE;
    
EXCEPTION
    WHEN OTHERS THEN
        RETURN QUERY SELECT -99, ('Error: ' || SQLERRM)::VARCHAR(255), NULL::INTEGER;
END;
$$ LANGUAGE plpgsql;

-- 使用示例
SELECT * FROM manage_user('CREATE', NULL, 'john_doe', 'john@example.com', 'John', 'Doe');
SELECT * FROM manage_user('UPDATE', 1, NULL, 'john_new@example.com');
SELECT * FROM manage_user('DELETE', 1);