跳到主要内容

PostgreSQL 存储过程和函数

PostgreSQL 提供了强大的存储过程和函数功能，允许你在数据库服务器端封装业务逻辑。本章将介绍如何创建和使用存储过程、函数，以及 PostgreSQL 特有的 PL/pgSQL 语言。

函数与存储过程的区别​

在 PostgreSQL 中，函数（Function）和存储过程（Procedure）有以下区别：

特性函数存储过程
返回值必须返回值可以不返回值
事务控制不能包含 COMMIT/ROLLBACK可以包含事务控制语句
调用方式SELECT func()CALL proc()
SQL 语句中可以在 SELECT 中使用不能在 SQL 语句中使用
参数模式IN、OUT、INOUTIN、OUT、INOUT

PL/pgSQL 语言​

PL/pgSQL 是 PostgreSQL 的过程语言，是编写函数和存储过程的首选语言。

基本结构​

CREATE [OR REPLACE] FUNCTION function_name(param1 type, param2 type)
RETURNS return_type AS $$
DECLARE
    -- 变量声明
    variable_name data_type;
BEGIN
    -- 函数体
    RETURN value;
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(10, 20);  -- 结果: 30

变量和数据类型​

变量声明​

CREATE OR REPLACE FUNCTION variable_demo()
RETURNS VOID AS $$
DECLARE
    -- 基本类型声明
    user_name VARCHAR(50);
    age INTEGER := 25;  -- 带默认值
    salary NUMERIC(10, 2) DEFAULT 0.00;
    is_active BOOLEAN := TRUE;
    
    -- 使用表字段类型
    customer_record customers%ROWTYPE;  -- 整行类型
    customer_name customers.name%TYPE;   -- 字段类型
    
    -- 常量
    max_size CONSTANT INTEGER := 100;
    
    -- 行类型变量
    user_data RECORD;
BEGIN
    -- 赋值
    user_name := '张三';
    age := age + 1;
    
    -- 打印调试信息（在客户端显示）
    RAISE NOTICE '用户名: %, 年龄: %', user_name, age;
END;
$$ LANGUAGE plpgsql;

变量作用域​

CREATE OR REPLACE FUNCTION scope_demo()
RETURNS INTEGER AS $$
DECLARE
    outer_var INTEGER := 10;
BEGIN
    -- 外部变量可见
    RAISE NOTICE '外部变量: %', outer_var;
    
    -- 嵌套块
    DECLARE
        inner_var INTEGER := 20;
        outer_var INTEGER := 30;  -- 遮蔽外部变量
    BEGIN
        RAISE NOTICE '内部变量: %, 遮蔽的外部变量: %', inner_var, outer_var;
    END;
    
    -- 外部变量不变
    RAISE NOTICE '回到外部: %', outer_var;
    
    RETURN outer_var;
END;
$$ LANGUAGE plpgsql;

控制结构​

条件语句​

CREATE OR REPLACE FUNCTION get_grade(score NUMERIC)
RETURNS VARCHAR AS $$
BEGIN
    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;
$$ LANGUAGE plpgsql;

-- CASE 语句
CREATE OR REPLACE FUNCTION get_status_text(status INTEGER)
RETURNS VARCHAR AS $$
BEGIN
    RETURN CASE status
        WHEN 0 THEN '待处理'
        WHEN 1 THEN '处理中'
        WHEN 2 THEN '已完成'
        WHEN 3 THEN '已取消'
        ELSE '未知状态'
    END;
END;
$$ LANGUAGE plpgsql;

循环语句​

-- 基本循环
CREATE OR REPLACE FUNCTION loop_demo(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
    i INTEGER := 1;
BEGIN
    LOOP
        sum_val := sum_val + i;
        i := i + 1;
        EXIT WHEN i > n;  -- 退出条件
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

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

-- FOR 循环（整数范围）
CREATE OR REPLACE FUNCTION for_demo(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        sum_val := sum_val + i;
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

-- FOR 循环（遍历查询结果）
CREATE OR REPLACE FUNCTION process_orders()
RETURNS VOID AS $$
DECLARE
    order_record RECORD;
BEGIN
    FOR order_record IN 
        SELECT id, customer_id, total_amount 
        FROM orders 
        WHERE status = 'pending'
    LOOP
        -- 处理每个订单
        RAISE NOTICE '处理订单 %, 金额: %', 
            order_record.id, order_record.total_amount;
        
        -- 更新订单状态
        UPDATE orders SET status = 'processing' 
        WHERE id = order_record.id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

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

SELECT array_demo(ARRAY[1, 2, 3, 4, 5]);  -- 结果: 15

CONTINUE 语句​

-- 跳过特定迭代
CREATE OR REPLACE FUNCTION skip_even_numbers(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    sum_val INTEGER := 0;
BEGIN
    FOR i IN 1..n LOOP
        -- 跳过偶数
        IF i % 2 = 0 THEN
            CONTINUE;
        END IF;
        sum_val := sum_val + i;
    END LOOP;
    RETURN sum_val;
END;
$$ LANGUAGE plpgsql;

参数模式​

IN 参数（默认）​

CREATE OR REPLACE FUNCTION greet(name VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN '你好, ' || name || '!';
END;
$$ LANGUAGE plpgsql;

SELECT greet('张三');  -- 你好, 张三!

OUT 参数​

CREATE OR REPLACE FUNCTION get_user_info(
    user_id INTEGER,
    OUT user_name VARCHAR,
    OUT user_email VARCHAR
)
AS $$
BEGIN
    SELECT name, email INTO user_name, user_email
    FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user_info(1);

INOUT 参数​

CREATE OR REPLACE FUNCTION double_value(INOUT val INTEGER)
AS $$
BEGIN
    val := val * 2;
END;
$$ LANGUAGE plpgsql;

SELECT double_value(5);  -- 10

多个 OUT 参数返回记录​

CREATE OR REPLACE FUNCTION get_order_summary(
    p_customer_id INTEGER,
    OUT total_orders INTEGER,
    OUT total_amount NUMERIC,
    OUT avg_amount NUMERIC
)
AS $$
BEGIN
    SELECT 
        COUNT(*),
        COALESCE(SUM(total_amount), 0),
        COALESCE(AVG(total_amount), 0)
    INTO total_orders, total_amount, avg_amount
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_order_summary(1);

返回集合​

RETURNS SETOF​

-- 返回多行
CREATE OR REPLACE FUNCTION get_high_value_customers(min_total NUMERIC)
RETURNS SETOF customers AS $$
BEGIN
    RETURN QUERY
    SELECT c.*
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
    HAVING SUM(o.total_amount) >= min_total;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_high_value_customers(500);

RETURNS TABLE​

-- 返回表格形式的结果
CREATE OR REPLACE FUNCTION get_customer_stats()
RETURNS TABLE(
    customer_id INTEGER,
    customer_name VARCHAR,
    order_count BIGINT,
    total_spent NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        c.id,
        c.name,
        COUNT(o.id),
        COALESCE(SUM(o.total_amount), 0)
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
    ORDER BY total_spent DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_customer_stats();

异常处理​

基本异常捕获​

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 '除数不能为零';
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

SELECT safe_divide(10, 0);  -- 返回 NULL 并打印通知

事务控制​

CREATE OR REPLACE FUNCTION transfer_funds(
    from_account INTEGER,
    to_account INTEGER,
    amount NUMERIC
)
RETURNS BOOLEAN AS $$
BEGIN
    -- 检查余额
    IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN
        RAISE EXCEPTION '余额不足';
    END IF;
    
    -- 扣款
    UPDATE accounts SET balance = balance - amount 
    WHERE id = from_account;
    
    -- 存款
    UPDATE accounts SET balance = balance + amount 
    WHERE id = to_account;
    
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE '转账失败: %', SQLERRM;
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

自定义异常​

CREATE OR REPLACE FUNCTION validate_order(
    p_customer_id INTEGER,
    p_amount NUMERIC
)
RETURNS INTEGER AS $$
DECLARE
    v_customer_exists BOOLEAN;
BEGIN
    -- 检查客户是否存在
    SELECT EXISTS(SELECT 1 FROM customers WHERE id = p_customer_id)
    INTO v_customer_exists;
    
    IF NOT v_customer_exists THEN
        RAISE EXCEPTION '客户 % 不存在', p_customer_id
            USING HINT = '请检查客户ID是否正确',
                  ERRCODE = 'P0001';
    END IF;
    
    -- 检查金额是否有效
    IF p_amount <= 0 THEN
        RAISE EXCEPTION '订单金额必须大于零';
    END IF;
    
    -- 创建订单
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (p_customer_id, p_amount, 'pending')
    RETURNING id INTO validate_order;
    
    RETURN validate_order;
END;
$$ LANGUAGE plpgsql;

存储过程​

创建存储过程​

-- 创建存储过程
CREATE OR REPLACE PROCEDURE process_monthly_report(
    p_year INTEGER,
    p_month INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
    report_date DATE;
BEGIN
    report_date := make_date(p_year, p_month, 1);
    
    -- 清理旧报告
    DELETE FROM monthly_reports 
    WHERE report_month = report_date;
    
    -- 生成新报告
    INSERT INTO monthly_reports (report_month, total_sales, order_count)
    SELECT 
        report_date,
        SUM(total_amount),
        COUNT(*)
    FROM orders
    WHERE EXTRACT(YEAR FROM order_date) = p_year
      AND EXTRACT(MONTH FROM order_date) = p_month;
    
    -- 存储过程可以包含事务控制
    COMMIT;
    
    RAISE NOTICE '月度报告已生成: %-%', p_year, p_month;
END;
$$;

-- 调用存储过程
CALL process_monthly_report(2024, 1);

带输出参数的存储过程​

CREATE OR REPLACE PROCEDURE get_customer_balance(
    p_customer_id INTEGER,
    OUT p_balance NUMERIC,
    OUT p_order_count INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        COALESCE(SUM(total_amount), 0),
        COUNT(*)
    INTO p_balance, p_order_count
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$;

-- 调用
CALL get_customer_balance(1, NULL, NULL);

动态 SQL​

EXECUTE 语句​

CREATE OR REPLACE FUNCTION get_table_count(table_name VARCHAR)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT;
BEGIN
    EXECUTE format('SELECT COUNT(*) FROM %I', table_name)
    INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT get_table_count('users');

使用参数​

CREATE OR REPLACE FUNCTION search_orders(
    p_column VARCHAR,
    p_value VARCHAR
)
RETURNS TABLE(id INTEGER, total_amount NUMERIC) AS $$
BEGIN
    RETURN QUERY EXECUTE format(
        'SELECT id, total_amount FROM orders WHERE %I = $1',
        p_column
    ) USING p_value;
END;
$$ LANGUAGE plpgsql;

触发器函数​

触发器函数是特殊的函数，用于在表上执行特定操作时自动运行。

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_modified_time()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_modified_time
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_modified_time();

游标​

游标用于处理大量数据，可以逐行获取结果。

CREATE OR REPLACE FUNCTION process_large_dataset()
RETURNS VOID AS $$
DECLARE
    cur CURSOR FOR SELECT * FROM large_table;
    rec RECORD;
    batch_count INTEGER := 0;
BEGIN
    OPEN cur;
    
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        -- 处理每条记录
        -- ...
        
        batch_count := batch_count + 1;
        
        -- 每 1000 条提交一次
        IF batch_count % 1000 = 0 THEN
            COMMIT;
            RAISE NOTICE '已处理 % 条记录', batch_count;
        END IF;
    END LOOP;
    
    CLOSE cur;
    RAISE NOTICE '处理完成，共 % 条记录', batch_count;
END;
$$ LANGUAGE plpgsql;

函数管理​

查看函数定义​

-- 查看函数列表
\df

-- 查看函数定义
\df+ function_name

-- 使用 SQL 查询
SELECT prosrc FROM pg_proc 
WHERE proname = 'function_name';

修改函数​

-- 修改函数定义
CREATE OR REPLACE FUNCTION function_name(...)
...

-- 修改函数所有者
ALTER FUNCTION function_name(INTEGER) OWNER TO new_owner;

-- 重命名函数
ALTER FUNCTION function_name(INTEGER) RENAME TO new_name;

删除函数​

-- 删除函数（需要指定参数类型）
DROP FUNCTION IF EXISTS function_name(INTEGER, VARCHAR);

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_name(INTEGER);

性能优化​

使用 SETOF 代替循环​

-- 不好的做法：循环插入
CREATE OR REPLACE FUNCTION bad_generate_series(n INTEGER)
RETURNS VOID AS $$
BEGIN
    FOR i IN 1..n LOOP
        INSERT INTO numbers (value) VALUES (i);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 好的做法：使用集合操作
CREATE OR REPLACE FUNCTION good_generate_series(n INTEGER)
RETURNS VOID AS $$
BEGIN
    INSERT INTO numbers (value)
    SELECT generate_series(1, n);
END;
$$ LANGUAGE plpgsql;

减少上下文切换​

-- 使用 RETURN QUERY 代替循环
CREATE OR REPLACE FUNCTION efficient_query()
RETURNS SETOF orders AS $$
BEGIN
    RETURN QUERY SELECT * FROM orders WHERE status = 'pending';
END;
$$ LANGUAGE plpgsql;

PostgreSQL 18 新特性​

虚拟生成列​

PostgreSQL 18 引入了虚拟生成列（Virtual Generated Columns），这是生成列的新默认行为。虚拟生成列在读取时计算值，而不是在写入时存储值。

-- 创建表时定义虚拟生成列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),
    quantity INTEGER,
    -- 虚拟生成列（默认）：读取时计算
    total_value NUMERIC GENERATED ALWAYS AS (price * quantity) VIRTUAL,
    -- 存储生成列：写入时计算并存储
    total_value_stored NUMERIC GENERATED ALWAYS AS (price * quantity) STORED
);

-- 插入数据（不能为生成列指定值）
INSERT INTO products (price, quantity) VALUES (100, 5);

-- 查询时自动计算虚拟生成列
SELECT id, price, quantity, total_value FROM products;
-- 结果：id=1, price=100, quantity=5, total_value=500

-- 更新数据后，虚拟生成列自动反映新值
UPDATE products SET price = 120 WHERE id = 1;
SELECT total_value FROM products WHERE id = 1;
-- 结果：600（120 * 5）

虚拟生成列 vs 存储生成列：

特性虚拟生成列（VIRTUAL）存储生成列（STORED）
计算时机读取时计算写入时计算并存储
存储空间不占用额外空间占用存储空间
更新开销无额外开销需要重新计算存储
索引支持不能直接索引可以创建索引
适用场景频繁更新、读取较少读取频繁、更新较少
-- 使用场景示例：计算年龄
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    birth_date DATE,
    -- 虚拟生成列：自动计算年龄
    age INTEGER GENERATED ALWAYS AS (
        EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date))::INTEGER
    ) VIRTUAL
);

INSERT INTO users (birth_date) VALUES ('1990-05-15');
SELECT birth_date, age FROM users;
-- 年龄会随着时间自动更新

-- 使用场景示例：格式化输出
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    -- 虚拟生成列：全名
    full_name VARCHAR(101) GENERATED ALWAYS AS (
        first_name || ' ' || last_name
    ) VIRTUAL
);

RETURNING 支持 OLD 和 NEW​

PostgreSQL 18 增强了 RETURNING 子句，现在可以在 INSERT、UPDATE、DELETE 和 MERGE 语句中使用 OLD 和 NEW 别名来获取修改前后的值。

-- 创建测试表
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance NUMERIC(10, 2) NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO accounts (balance) VALUES (1000);

-- UPDATE：获取修改前后的值
UPDATE accounts 
SET balance = balance + 100 
WHERE id = 1
RETURNING 
    OLD.balance AS old_balance,
    NEW.balance AS new_balance,
    NEW.balance - OLD.balance AS change;
-- 结果：old_balance=1000, new_balance=1100, change=100

-- INSERT：NEW 可用，OLD 返回 NULL
INSERT INTO accounts (balance) VALUES (2000)
RETURNING 
    OLD.balance AS old_balance,  -- NULL
    NEW.balance AS new_balance;   -- 2000

-- DELETE：OLD 可用，NEW 返回 NULL
DELETE FROM accounts WHERE id = 1
RETURNING 
    OLD.balance AS deleted_balance,  -- 实际值
    NEW.balance AS new_balance;       -- NULL

-- MERGE：根据操作类型返回不同值
MERGE INTO accounts a
USING (VALUES (1, 1500)) AS v(id, new_balance)
ON a.id = v.id
WHEN MATCHED THEN
    UPDATE SET balance = v.new_balance
WHEN NOT MATCHED THEN
    INSERT (id, balance) VALUES (v.id, v.new_balance)
RETURNING 
    action,
    OLD.balance AS old_balance,
    NEW.balance AS new_balance;

使用场景：

-- 场景1：记录变更历史
CREATE TABLE balance_history (
    id SERIAL PRIMARY KEY,
    account_id INTEGER,
    old_balance NUMERIC,
    new_balance NUMERIC,
    change_time TIMESTAMP DEFAULT NOW()
);

-- 在触发器中使用或直接记录
WITH updated AS (
    UPDATE accounts 
    SET balance = balance * 1.1 
    WHERE id = 1
    RETURNING id, OLD.balance, NEW.balance
)
INSERT INTO balance_history (account_id, old_balance, new_balance)
SELECT id, old_balance, new_balance FROM updated;

-- 场景2：计算变更差异
UPDATE products 
SET price = price * 1.05
RETURNING 
    name,
    OLD.price AS old_price,
    NEW.price AS new_price,
    ROUND((NEW.price - OLD.price) / OLD.price * 100, 2) AS pct_change;

-- 场景3：审计日志
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT,
    operation TEXT,
    old_data JSONB,
    new_data JSONB,
    changed_at TIMESTAMP DEFAULT NOW()
);

UPDATE accounts 
SET balance = 500 
WHERE id = 1
RETURNING 
    'accounts' AS table_name,
    'UPDATE' AS operation,
    to_jsonb(OLD) AS old_data,
    to_jsonb(NEW) AS new_data;

自定义别名：

-- 可以重命名 OLD 和 NEW 以避免标识符冲突
UPDATE accounts a
SET balance = a.balance + 100
RETURNING 
    old.balance AS previous,
    new.balance AS current;
-- 使用 old 和 new（小写）作为别名

小结​

本章我们学习了：

  1. 函数与存储过程的区别：返回值、事务控制、调用方式
  2. PL/pgSQL 语言：变量声明、控制结构
  3. 参数模式：IN、OUT、INOUT
  4. 返回集合：RETURNS SETOF、RETURNS TABLE
  5. 异常处理：捕获异常、自定义异常
  6. 动态 SQL：EXECUTE 语句
  7. 游标操作：处理大量数据
  8. 性能优化：减少上下文切换

练习​

  1. 创建一个函数，计算两个日期之间的工作日数量
  2. 创建一个存储过程，批量更新订单状态
  3. 创建一个触发器函数，记录用户登录日志
  4. 使用游标处理大数据集

参考资源​