PostgreSQL教程

调用函数

基本调用语法

函数使用SELECT语句调用：

-- 调用无参数函数
SELECT function_name();

-- 调用带参数函数
SELECT function_name(param1, param2, param3);

-- 调用带命名参数的函数
SELECT function_name(param1 => value1, param2 => value2);

-- 在表达式中调用函数
SELECT column1, function_name(column2) FROM table_name;

-- 在WHERE子句中调用函数
SELECT * FROM table_name WHERE function_name(column1) = 'value';

调用简单函数

-- 假设已创建以下函数
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

-- 在查询中使用函数
SELECT 
    id,
    username,
    add_numbers(score1, score2) AS total_score
FROM student_scores;

调用返回表的函数

-- 假设已创建以下函数
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();

-- 在FROM子句中使用函数
SELECT 
    user_id,
    username,
    email
FROM get_active_users()
WHERE username LIKE 'j%';

调用返回集合的函数

-- 假设已创建以下函数
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);

-- 与JOIN一起使用
SELECT 
    u.username,
    o.order_date,
    o.total_amount
FROM users u
JOIN get_user_orders(u.id) o ON true
WHERE u.id = 1;

调用存储过程

基本调用语法

存储过程使用CALL语句调用（PostgreSQL 11+）：

-- 调用无参数存储过程
CALL procedure_name();

-- 调用带参数存储过程
CALL procedure_name(param1, param2, param3);

-- 调用带命名参数的存储过程
CALL procedure_name(param1 => value1, param2 => value2);

调用简单存储过程

-- 假设已创建以下存储过程
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参数的存储过程

-- 假设已创建以下存储过程
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;
$$;

-- 调用带INOUT参数的存储过程
CALL update_user_stats(1, 0, 0.00);

-- 使用匿名代码块调用并获取结果
DO $$
DECLARE
    order_count INTEGER := 0;
    total_spent NUMERIC(12,2) := 0.00;
BEGIN
    CALL update_user_stats(1, order_count, total_spent);
    RAISE NOTICE 'User 1 has % orders totaling %', order_count, total_spent;
END;
$$;

参数传递方式

位置参数

-- 按位置传递参数
SELECT calculate_discount(100.00, 20.00);

-- 等同于
CALL create_user('john_doe', 'john@example.com', 'John', 'Doe');

命名参数

-- 使用命名参数传递
SELECT calculate_discount(price => 100.00, discount_percent => 20.00);

-- 混合使用位置参数和命名参数（命名参数必须在位置参数之后）
SELECT calculate_discount(100.00, discount_percent => 20.00);

-- 存储过程的命名参数
CALL create_user(
    p_username => 'jane_doe',
    p_email => 'jane@example.com',
    p_first_name => 'Jane',
    p_last_name => 'Doe'
);

默认参数

-- 假设已创建带默认参数的函数
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); -- discount_percent使用默认值10.00

-- 覆盖默认参数
SELECT calculate_discount(100.00, 20.00); -- 指定discount_percent为20.00

结果处理

处理函数返回值

-- 将函数结果存储在变量中（在PL/pgSQL块中）
DO $$
DECLARE
    result INTEGER;
BEGIN
    SELECT add_numbers(5, 3) INTO result;
    RAISE NOTICE 'Result: %', result;
END;
$$;

-- 在INSERT语句中使用函数
INSERT INTO calculation_results (value1, value2, sum_result)
SELECT 5, 3, add_numbers(5, 3);

-- 在UPDATE语句中使用函数
UPDATE products 
SET discounted_price = calculate_discount(price, 15.00)
WHERE category = 'Electronics';

处理存储过程结果

-- 使用匿名代码块处理存储过程结果
DO $$
DECLARE
    order_count INTEGER := 0;
    total_spent NUMERIC(12,2) := 0.00;
BEGIN
    CALL update_user_stats(1, order_count, total_spent);
    
    IF order_count > 10 THEN
        RAISE NOTICE 'VIP user with % orders totaling %', order_count, total_spent;
    ELSE
        RAISE NOTICE 'Regular user with % orders totaling %', order_count, total_spent;
    END IF;
END;
$$;

实际应用场景

批量操作

-- 创建批量处理存储过程
CREATE OR REPLACE PROCEDURE batch_process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    order_record RECORD;
    processed_count INTEGER := 0;
BEGIN
    -- 处理待处理订单
    FOR order_record IN 
        SELECT id, user_id, total_amount 
        FROM orders 
        WHERE status = 'pending'
    LOOP
        -- 更新订单状态
        UPDATE orders 
        SET status = 'processed', processed_at = CURRENT_TIMESTAMP
        WHERE id = order_record.id;
        
        -- 更新用户统计
        UPDATE user_statistics 
        SET total_orders = total_orders + 1,
            total_spent = total_spent + order_record.total_amount
        WHERE user_id = order_record.user_id;
        
        processed_count := processed_count + 1;
    END LOOP;
    
    COMMIT;
    RAISE NOTICE 'Processed % orders', processed_count;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 调用批量处理存储过程
CALL batch_process_orders();

数据迁移

-- 创建数据迁移存储过程
CREATE OR REPLACE PROCEDURE migrate_user_data()
LANGUAGE plpgsql
AS $$
DECLARE
    user_record RECORD;
    migrated_count INTEGER := 0;
BEGIN
    -- 迁移用户数据
    FOR user_record IN 
        SELECT id, old_username, old_email, created_date
        FROM old_users 
        WHERE migrated = FALSE
    LOOP
        -- 插入到新表
        INSERT INTO new_users (username, email, created_at, source_system)
        VALUES (
            user_record.old_username,
            user_record.old_email,
            user_record.created_date,
            'legacy_system'
        );
        
        -- 标记为已迁移
        UPDATE old_users 
        SET migrated = TRUE, migrated_at = CURRENT_TIMESTAMP
        WHERE id = user_record.id;
        
        migrated_count := migrated_count + 1;
    END LOOP;
    
    COMMIT;
    RAISE NOTICE 'Migrated % users', migrated_count;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 调用数据迁移存储过程
CALL migrate_user_data();

报告生成

-- 创建返回报告数据的函数
CREATE OR REPLACE FUNCTION generate_sales_report(start_date DATE, end_date DATE)
RETURNS TABLE(
    period TEXT,
    total_orders BIGINT,
    total_sales NUMERIC(15,2),
    avg_order_value NUMERIC(10,2),
    top_product VARCHAR(100)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        CONCAT(start_date, ' to ', end_date) AS period,
        COUNT(*) AS total_orders,
        SUM(total_amount) AS total_sales,
        ROUND(AVG(total_amount), 2) AS avg_order_value,
        (SELECT product_name 
         FROM order_items oi 
         JOIN products p ON oi.product_id = p.id
         WHERE oi.order_id IN (
             SELECT id FROM orders 
             WHERE order_date BETWEEN start_date AND end_date
         )
         GROUP BY product_name 
         ORDER BY SUM(quantity) DESC 
         LIMIT 1) AS top_product
    FROM orders
    WHERE order_date BETWEEN start_date AND end_date;
END;
$$ LANGUAGE plpgsql;

-- 调用报告函数
SELECT * FROM generate_sales_report('2023-10-01', '2023-10-31');

错误处理

处理函数异常

-- 在匿名代码块中处理函数异常
DO $$
DECLARE
    result NUMERIC;
BEGIN
    BEGIN
        SELECT safe_divide(10, 0) INTO result;
        RAISE NOTICE 'Result: %', result;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Function call failed: %', SQLERRM;
    END;
END;
$$;

处理存储过程异常

-- 在匿名代码块中处理存储过程异常
DO $$
BEGIN
    BEGIN
        CALL create_user(NULL, NULL, NULL, NULL);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Procedure call failed: %', SQLERRM;
    END;
END;
$$;

性能考虑

调用优化

-- 避免在循环中重复调用函数
-- 不好的做法
SELECT 
    id,
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 好的做法
SELECT 
    u.id,
    u.username,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

批量调用

-- 使用数组参数进行批量操作
CREATE OR REPLACE FUNCTION process_user_batch(user_ids INTEGER[])
RETURNS INTEGER AS $$
DECLARE
    user_id INTEGER;
    processed_count INTEGER := 0;
BEGIN
    FOREACH user_id IN ARRAY user_ids LOOP
        -- 处理每个用户
        UPDATE users 
        SET last_processed = CURRENT_TIMESTAMP
        WHERE id = user_id;
        
        processed_count := processed_count + 1;
    END LOOP;
    
    RETURN processed_count;
END;
$$ LANGUAGE plpgsql;

-- 调用批量处理函数
SELECT process_user_batch(ARRAY[1, 2, 3, 4, 5]);

-- 创建完整的订单处理系统
CREATE OR REPLACE FUNCTION process_order_with_notification(
    p_user_id INTEGER,
    p_products JSONB
)
RETURNS TABLE(
    order_id INTEGER,
    status VARCHAR(50),
    message TEXT
) AS $$
DECLARE
    v_order_id INTEGER;
    v_error_message TEXT;
BEGIN
    -- 调用订单处理函数
    BEGIN
        SELECT process_order(p_user_id, p_products) INTO v_order_id;
        
        RETURN QUERY SELECT 
            v_order_id AS order_id,
            'SUCCESS'::VARCHAR(50) AS status,
            'Order processed successfully'::TEXT AS message;
            
    EXCEPTION
        WHEN OTHERS THEN
            v_error_message := SQLERRM;
            
            RETURN QUERY SELECT 
                NULL::INTEGER AS order_id,
                'ERROR'::VARCHAR(50) AS status,
                ('Order processing failed: ' || v_error_message)::TEXT AS message;
    END;
END;
$$ LANGUAGE plpgsql;

-- 创建通知发送存储过程
CREATE OR REPLACE PROCEDURE send_order_notification(
    p_order_id INTEGER,
    p_user_id INTEGER,
    p_notification_type VARCHAR(20) DEFAULT 'email'
)
LANGUAGE plpgsql
AS $$
DECLARE
    user_email VARCHAR(255);
    order_total NUMERIC(10,2);
BEGIN
    -- 获取用户邮箱和订单总额
    SELECT u.email, o.total_amount 
    INTO user_email, order_total
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.id = p_user_id AND o.id = p_order_id;
    
    -- 根据通知类型发送通知
    CASE p_notification_type
        WHEN 'email' THEN
            INSERT INTO email_queue (recipient, subject, body, created_at)
            VALUES (
                user_email,
                'Order Confirmation',
                'Your order #' || p_order_id || ' for $' || order_total || ' has been confirmed.',
                CURRENT_TIMESTAMP
            );
        WHEN 'sms' THEN
            INSERT INTO sms_queue (recipient, message, created_at)
            VALUES (
                user_email,
                'Order #' || p_order_id || ' confirmed. Total: $' || order_total,
                CURRENT_TIMESTAMP
            );
        ELSE
            RAISE NOTICE 'Unknown notification type: %', p_notification_type;
    END CASE;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 完整的订单处理流程
CREATE OR REPLACE PROCEDURE complete_order_process(
    p_user_id INTEGER,
    p_products JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    order_result RECORD;
BEGIN
    -- 处理订单
    SELECT * FROM process_order_with_notification(p_user_id, p_products) INTO order_result;
    
    IF order_result.status = 'SUCCESS' THEN
        -- 发送通知
        CALL send_order_notification(order_result.order_id, p_user_id, 'email');
        CALL send_order_notification(order_result.order_id, p_user_id, 'sms');
        
        RAISE NOTICE 'Order % processed successfully and notifications sent', order_result.order_id;
    ELSE
        RAISE EXCEPTION 'Order processing failed: %', order_result.message;
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;

-- 调用完整的订单处理流程
CALL complete_order_process(
    1, 
    '[{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}]'
);