pgsql-12 存储过程与函数

2026-03-16 约 2064 字预计阅读 5 分钟

12 - 存储过程与函数

1. 📖 概述

PostgreSQL支持创建存储过程(Procedure)和函数(Function),可以封装复杂的业务逻辑。本章介绍PL/pgSQL语言编写存储过程和函数。

2. 🔧 函数基础

2.1 创建简单函数

-- 基本函数
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 hello(name TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN 'Hello, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

SELECT hello('Alice');  -- 返回'Hello, Alice!'

2.2 函数参数

-- 命名参数
CREATE OR REPLACE FUNCTION calculate_discount(
    original_price NUMERIC,
    discount_rate NUMERIC DEFAULT 0.1
)
RETURNS NUMERIC AS $$
BEGIN
    RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;

-- 调用方式
SELECT calculate_discount(100, 0.2);  -- 80
SELECT calculate_discount(100);       -- 90 (使用默认值)
SELECT calculate_discount(original_price => 100, discount_rate => 0.15);  -- 命名参数

-- OUT参数
CREATE OR REPLACE FUNCTION get_user_stats(
    p_user_id INTEGER,
    OUT order_count INTEGER,
    OUT total_spent NUMERIC
) AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO order_count, total_spent
    FROM orders
    WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_user_stats(1);

2.3 返回类型

-- 返回表类型
CREATE OR REPLACE FUNCTION get_expensive_products(min_price NUMERIC)
RETURNS TABLE(id INTEGER, name TEXT, price NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, p.name, p.price
    FROM products p
    WHERE p.price >= min_price
    ORDER BY p.price DESC;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM get_expensive_products(100);

-- 返回SETOF (多行)
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id INTEGER)
RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM orders WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- 返回记录类型
CREATE TYPE user_summary AS (
    username TEXT,
    order_count INTEGER,
    total_spent NUMERIC
);

CREATE OR REPLACE FUNCTION get_user_summary(p_user_id INTEGER)
RETURNS user_summary AS $$
DECLARE
    result user_summary;
BEGIN
    SELECT u.username, COUNT(o.id), COALESCE(SUM(o.total_amount), 0)
    INTO result
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = p_user_id
    GROUP BY u.username;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

3. 📝 PL/pgSQL语法

3.1 变量声明

CREATE OR REPLACE FUNCTION demo_variables()
RETURNS TEXT AS $$
DECLARE
    my_int INTEGER := 10;
    my_text TEXT := 'Hello';
    my_date DATE := CURRENT_DATE;
    my_record RECORD;
    user_count INTEGER;
BEGIN
    -- 变量赋值
    my_int := 20;
    my_text := 'World';

    -- 从查询赋值
    SELECT COUNT(*) INTO user_count FROM users;

    -- 返回
    RETURN format('Count: %s, Text: %s', user_count, my_text);
END;
$$ LANGUAGE plpgsql;

3.2 条件语句

CREATE OR REPLACE FUNCTION get_price_category(price NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF price < 20 THEN
        RETURN 'Cheap';
    ELSIF price < 50 THEN
        RETURN 'Moderate';
    ELSIF price < 100 THEN
        RETURN 'Expensive';
    ELSE
        RETURN 'Premium';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- CASE语句
CREATE OR REPLACE FUNCTION get_day_type(day_num INTEGER)
RETURNS TEXT AS $$
BEGIN
    RETURN CASE day_num
        WHEN 1, 7 THEN 'Weekend'
        WHEN 2, 3, 4, 5, 6 THEN 'Weekday'
        ELSE 'Invalid'
    END;
END;
$$ LANGUAGE plpgsql;

3.3 循环语句

-- LOOP循环
CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
    i INTEGER := 1;
BEGIN
    LOOP
        EXIT WHEN i > n;
        result := result * i;
        i := i + 1;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- WHILE循环
CREATE OR REPLACE FUNCTION factorial_while(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
    i INTEGER := 1;
BEGIN
    WHILE i <= n LOOP
        result := result * i;
        i := i + 1;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- FOR循环
CREATE OR REPLACE FUNCTION factorial_for(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
BEGIN
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- FOREACH循环(数组)
CREATE OR REPLACE FUNCTION sum_array(arr INTEGER[])
RETURNS INTEGER AS $$
DECLARE
    total INTEGER := 0;
    element INTEGER;
BEGIN
    FOREACH element IN ARRAY arr LOOP
        total := total + element;
    END LOOP;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT sum_array(ARRAY[1, 2, 3, 4, 5]);  -- 15

3.4 异常处理

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Division by zero attempted';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 自定义异常
CREATE OR REPLACE FUNCTION validate_age(age INTEGER)
RETURNS BOOLEAN AS $$
BEGIN
    IF age < 0 THEN
        RAISE EXCEPTION 'Age cannot be negative: %', age;
    END IF;

    IF age < 18 THEN
        RAISE EXCEPTION 'Must be 18 or older'
            USING HINT = 'Check age requirement';
    END IF;

    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Validation failed: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

4. 🔄 存储过程

PostgreSQL 11+支持存储过程(PROCEDURE),与函数的区别是可以提交/回滚事务。

-- 创建存储过程
CREATE OR REPLACE PROCEDURE transfer_money(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    -- 检查余额
    IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- 扣款
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;

    -- 入账
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;

    -- 记录日志
    INSERT INTO transfer_logs (from_account, to_account, amount, created_at)
    VALUES (from_account, to_account, amount, NOW());

    COMMIT;  -- 存储过程可以显式提交
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 失败时回滚
        RAISE;
END;
$$;

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

5. 💡 实战示例

5.1 订单处理函数

CREATE OR REPLACE FUNCTION create_order(
    p_user_id INTEGER,
    p_items JSONB  -- [{product_id: 1, quantity: 2}, ...]
)
RETURNS INTEGER AS $$
DECLARE
    v_order_id INTEGER;
    v_item JSONB;
    v_product_price NUMERIC;
    v_subtotal NUMERIC := 0;
BEGIN
    -- 创建订单
    INSERT INTO orders (user_id, total_amount, status, created_at)
    VALUES (p_user_id, 0, 'pending', NOW())
    RETURNING id INTO v_order_id;

    -- 处理订单项
    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items) LOOP
        -- 获取产品价格
        SELECT price INTO v_product_price
        FROM products
        WHERE id = (v_item->>'product_id')::INTEGER;

        IF v_product_price IS NULL THEN
            RAISE EXCEPTION 'Product not found: %', v_item->>'product_id';
        END IF;

        -- 检查库存
        IF (SELECT stock FROM products WHERE id = (v_item->>'product_id')::INTEGER)
           < (v_item->>'quantity')::INTEGER THEN
            RAISE EXCEPTION 'Insufficient stock for product: %', v_item->>'product_id';
        END IF;

        -- 插入订单项
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (
            v_order_id,
            (v_item->>'product_id')::INTEGER,
            (v_item->>'quantity')::INTEGER,
            v_product_price
        );

        -- 更新库存
        UPDATE products
        SET stock = stock - (v_item->>'quantity')::INTEGER
        WHERE id = (v_item->>'product_id')::INTEGER;

        -- 累计金额
        v_subtotal := v_subtotal + (v_product_price * (v_item->>'quantity')::INTEGER);
    END LOOP;

    -- 更新订单总额
    UPDATE orders SET total_amount = v_subtotal WHERE id = v_order_id;

    RETURN v_order_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Order creation failed: %', SQLERRM;
        RAISE;
END;
$$ LANGUAGE plpgsql;

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

5.2 数据清理函数

CREATE OR REPLACE FUNCTION cleanup_old_data(days_to_keep INTEGER)
RETURNS TABLE(table_name TEXT, rows_deleted BIGINT) AS $$
DECLARE
    deleted_count BIGINT;
BEGIN
    -- 清理旧订单
    DELETE FROM orders WHERE created_at < NOW() - (days_to_keep || ' days')::INTERVAL
        AND status IN ('cancelled', 'completed');
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    table_name := 'orders';
    rows_deleted := deleted_count;
    RETURN NEXT;

    -- 清理旧日志
    DELETE FROM activity_logs WHERE created_at < NOW() - (days_to_keep || ' days')::INTERVAL;
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    table_name := 'activity_logs';
    rows_deleted := deleted_count;
    RETURN NEXT;

    -- 清理过期会话
    DELETE FROM sessions WHERE expires_at < NOW();
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    table_name := 'sessions';
    rows_deleted := deleted_count;
    RETURN NEXT;

    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 使用
SELECT * FROM cleanup_old_data(90);

6. 🎯 最佳实践

6.1 1. 使用STABLE/VOLATILE/IMMUTABLE

-- IMMUTABLE: 相同输入总是返回相同结果,可以优化
CREATE OR REPLACE FUNCTION add(a INTEGER, b INTEGER)
RETURNS INTEGER
IMMUTABLE AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- STABLE: 在单次查询中返回相同结果
CREATE OR REPLACE FUNCTION get_current_user_id()
RETURNS INTEGER
STABLE AS $$
BEGIN
    RETURN current_setting('app.user_id')::INTEGER;
END;
$$ LANGUAGE plpgsql;

-- VOLATILE: 可能返回不同结果(默认)
CREATE OR REPLACE FUNCTION create_audit_log(msg TEXT)
RETURNS VOID
VOLATILE AS $$
BEGIN
    INSERT INTO audit_logs (message, created_at) VALUES (msg, NOW());
END;
$$ LANGUAGE plpgsql;

6.2 2. 错误处理

-- 始终处理可能的异常
CREATE OR REPLACE FUNCTION process_payment(order_id INTEGER, amount NUMERIC)
RETURNS BOOLEAN AS $$
DECLARE
    v_user_balance NUMERIC;
BEGIN
    -- 获取余额
    SELECT balance INTO STRICT v_user_balance
    FROM user_wallets
    WHERE user_id = (SELECT user_id FROM orders WHERE id = order_id);

    -- 检查余额
    IF v_user_balance < amount THEN
        RAISE EXCEPTION 'Insufficient balance';
    END IF;

    -- 扣款
    UPDATE user_wallets
    SET balance = balance - amount
    WHERE user_id = (SELECT user_id FROM orders WHERE id = order_id);

    -- 更新订单状态
    UPDATE orders SET status = 'paid' WHERE id = order_id;

    RETURN TRUE;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'User wallet not found';
        RETURN FALSE;
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'Multiple wallets found';
        RETURN FALSE;
    WHEN OTHERS THEN
        RAISE NOTICE 'Payment failed: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

6.3 3. 性能优化

-- 使用SQL而不是循环
-- 不好
CREATE OR REPLACE FUNCTION update_prices_bad()
RETURNS VOID AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id, price FROM products LOOP
        UPDATE products SET price = price * 1.1 WHERE id = r.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 好
CREATE OR REPLACE FUNCTION update_prices_good()
RETURNS VOID AS $$
BEGIN
    UPDATE products SET price = price * 1.1;
END;
$$ LANGUAGE plpgsql;

7. 🆚 PostgreSQL vs MySQL

特性	PostgreSQL	MySQL
存储过程	PROCEDURE	PROCEDURE
函数	FUNCTION	FUNCTION
语言	PL/pgSQL, SQL, Python等	SQL
返回表	RETURNS TABLE	不支持
OUT参数	支持	支持
异常处理	完整支持	LIMITED
事务控制	PROCEDURE支持	支持

8. 📚 下一步

学习触发器详解
了解事务与并发控制

关键要点:

函数必须有RETURNS,存储过程没有
PL/pgSQL支持变量、循环、异常处理
使用IMMUTABLE/STABLE优化性能
避免在循环中执行SQL
存储过程可以提交/回滚事务

目录

pgsql-12 存储过程与函数

12 - 存储过程与函数

1. 📖 概述

2. 🔧 函数基础

2.1 创建简单函数

2.2 函数参数

2.3 返回类型

3. 📝 PL/pgSQL语法

3.1 变量声明

3.2 条件语句

3.3 循环语句

3.4 异常处理

4. 🔄 存储过程

5. 💡 实战示例

5.1 订单处理函数

5.2 数据清理函数

6. 🎯 最佳实践

6.1 1. 使用STABLE/VOLATILE/IMMUTABLE

6.2 2. 错误处理

6.3 3. 性能优化

7. 🆚 PostgreSQL vs MySQL

8. 📚 下一步