PostgreSQL 函数与存储过程（Function / Procedure）详解

原创于 2025-09-09 17:20:03 发布 · 1.3k 阅读

32 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #函数 #存储过程 #学习

数据库专栏收录该内容

56 篇文章

订阅专栏

✅ PostgreSQL 函数与存储过程（Function / Procedure）详解

在 PostgreSQL 中，函数（Function） 和 存储过程（Procedure） 是用于封装可重用 SQL 逻辑的重要数据库对象。它们可以接受参数、执行复杂操作、返回结果，极大提升开发效率和数据一致性。

🧭 一、核心概念对比

特性	函数（Function）	存储过程（Procedure）
创建语句	`CREATE FUNCTION ...`	`CREATE PROCEDURE ...`（PG ≥ 11）
是否可返回值	✅ 必须声明返回类型（可返回多行、标量、表等）	❌ 不直接返回值（可通过 OUT 参数）
是否支持事务控制	❌ 函数内不能 COMMIT/ROLLBACK	✅ 支持内部事务（自治事务）
调用方式	`SELECT func(...)` 或 `SELECT * FROM func(...)`	`CALL proc(...)`
主要用途	计算、转换、查询封装	执行复杂业务逻辑、批量操作、ETL
是否可被 SQL 语句嵌套	✅ 可用于 SELECT/WHERE 等	❌ 不能直接嵌入 SQL 表达式

💡 简单理解：

函数 = 有返回值的“计算单元”
存储过程 = 无返回值的“操作脚本”（可含事务）

一、函数（Function）详解

✅ 1. 基本语法

CREATE [OR REPLACE] FUNCTION function_name (参数列表)
RETURNS 返回类型
LANGUAGE 语言
AS $$
-- 函数体
$$;

支持的语言：

sql：纯 SQL 语句
plpgsql：过程化 SQL（最常用，支持变量、循环、条件）
plpython, plperl, C 等（需扩展）

✅ 2. 函数返回类型

类型	说明	示例
`VOID`	无返回值（类似过程）	`RETURNS VOID`
标量类型	如 `INT`, `TEXT`, `BOOLEAN`	`RETURNS INTEGER`
`SETOF 类型`	返回多行单列	`RETURNS SETOF TEXT`
`TABLE(...)`	返回表结构（多行多列）	`RETURNS TABLE(id INT, name TEXT)`
`RECORD`	返回匿名记录（需配合 OUT）	`RETURNS RECORD`

✅ 3. 实用示例

示例 1：标量函数 —— 计算两数之和

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE sql
AS $$
    SELECT a + b;
$$;

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

示例 2：表函数 —— 返回用户列表

CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(id INT, name TEXT, email TEXT)
LANGUAGE sql
AS $$
    SELECT id, name, email
    FROM users
    WHERE is_active = true;
$$;

-- 调用（像表一样使用）
SELECT * FROM get_active_users();

示例 3：PL/pgSQL 函数 —— 带条件和变量

CREATE OR REPLACE FUNCTION get_user_level(salary NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    level TEXT;
BEGIN
    IF salary >= 10000 THEN
        level := '高级';
    ELSIF salary >= 5000 THEN
        level := '中级';
    ELSE
        level := '初级';
    END IF;
    RETURN level;
END;
$$;

-- 调用
SELECT name, get_user_level(salary) FROM employees;

示例 4：带 OUT 参数的函数

CREATE OR REPLACE FUNCTION get_user_info(
    user_id INT,
    OUT username TEXT,
    OUT useremail TEXT
)
LANGUAGE sql
AS $$
    SELECT name, email FROM users WHERE id = user_id;
$$;

-- 调用
SELECT * FROM get_user_info(1);
-- 或
SELECT (get_user_info(1)).*;

✅ 4. 删除函数

DROP FUNCTION IF EXISTS function_name(参数类型);
-- 示例：
DROP FUNCTION IF EXISTS add_numbers(INT, INT);

⚠️ 注意：PostgreSQL 重载函数，删除时需指定参数类型。

二、存储过程（Procedure）详解（PostgreSQL ≥ 11）

✅ 1. 基本语法

CREATE [OR REPLACE] PROCEDURE procedure_name (参数列表)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 过程体（可含 COMMIT / ROLLBACK）
END;
$$;

💡 存储过程必须使用 CALL 调用，不能用 SELECT。

✅ 2. 参数模式

模式	说明	示例
`IN`	输入参数（默认）	`user_id INT`
`OUT`	输出参数	`OUT result TEXT`
`INOUT`	输入输出参数	`INOUT counter INT`

✅ 3. 实用示例

示例 1：简单转账过程（含事务）

CREATE OR REPLACE PROCEDURE transfer_money(
    from_account INT,
    to_account INT,
    amount NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 扣款
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    -- 入账
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;

    -- 可选：记录日志
    INSERT INTO transfer_log (from_id, to_id, amount, transfer_time)
    VALUES (from_account, to_account, amount, NOW());

    COMMIT; -- 存储过程内可提交事务！
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '转账失败: %', SQLERRM;
END;
$$;

-- 调用
CALL transfer_money(1, 2, 500.00);

✅ 此例中，即使外部在事务中调用，过程内的 COMMIT 也会生效（自治事务）。

示例 2：带 OUT 参数的过程

CREATE OR REPLACE PROCEDURE get_user_count(
    OUT total_count INT,
    OUT active_count INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO total_count FROM users;
    SELECT COUNT(*) INTO active_count FROM users WHERE is_active = true;
END;
$$;

-- 调用
CALL get_user_count(NULL, NULL);
-- 或在匿名块中调用获取值
DO $$
DECLARE
    t INT;
    a INT;
BEGIN
    CALL get_user_count(t, a);
    RAISE NOTICE '总用户数: %, 活跃用户: %', t, a;
END $$;

✅ 4. 删除存储过程

DROP PROCEDURE IF EXISTS procedure_name(参数类型);
-- 示例：
DROP PROCEDURE IF EXISTS transfer_money(INT, INT, NUMERIC);

三、函数 vs 存储过程如何选择？

场景	推荐使用
需要返回计算结果	✅ 函数
需要在 SELECT 中调用	✅ 函数
需要执行 COMMIT/ROLLBACK	✅ 存储过程
执行批量数据处理、ETL、维护任务	✅ 存储过程
需要 OUT 参数返回多个值	两者均可
需要被触发器调用	✅ 函数

四、高级特性

✅ 1. 函数重载（Overload）

PostgreSQL 支持同名函数，只要参数类型或数量不同：

CREATE FUNCTION log_message(msg TEXT) ...
CREATE FUNCTION log_message(msg TEXT, level TEXT) ...
-- 调用时根据参数自动匹配

✅ 2. 返回表的函数（Table-Valued Function）

CREATE FUNCTION search_users(keyword TEXT)
RETURNS TABLE(id INT, name TEXT, email TEXT)
AS $$
    SELECT id, name, email
    FROM users
    WHERE name ILIKE '%' || keyword || '%'
       OR email ILIKE '%' || keyword || '%';
$$ LANGUAGE sql;

-- 调用
SELECT * FROM search_users('alice');

✅ 3. 使用 RETURNS SETOF RECORD（动态结构）

CREATE FUNCTION get_dynamic_data(tablename TEXT)
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(tablename);
END;
$$;

-- 调用时需指定列结构
SELECT * FROM get_dynamic_data('users') AS t(id INT, name TEXT);

✅ 4. 异常处理（EXCEPTION）

在 PL/pgSQL 中可捕获异常：

CREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE '除零错误';
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE NOTICE '未知错误: %', SQLERRM;
        RETURN NULL;
END;
$$;

✅ 5. 函数属性（VOLATILE, STABLE, IMMUTABLE）

用于优化器判断是否缓存结果：

CREATE FUNCTION current_time_func()
RETURNS TIMESTAMP
LANGUAGE sql
VOLATILE  -- 每次调用都重新计算（默认）
AS $$ SELECT NOW(); $$;

CREATE FUNCTION get_pi()
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE  -- 永远返回相同结果，可被优化器缓存
AS $$ SELECT 3.1415926; $$;

属性	含义	示例函数
`VOLATILE`	结果可能随时变（默认）	`NOW()`, `RANDOM()`
`STABLE`	在单个查询中结果不变	`UPPER()`, `LENGTH()`
`IMMUTABLE`	永远不变（相同输入→相同输出）	数学常量、纯计算函数

五、管理与调试

✅ 1. 查看已创建的函数/过程

-- 查看函数
\df [函数名]          -- psql 元命令
SELECT * FROM pg_proc WHERE proname = '函数名';

-- 查看存储过程（PG 11+）
\dfp [过程名]
SELECT * FROM pg_proc WHERE prokind = 'p'; -- p=procedure, f=function

✅ 2. 查看函数定义

-- 在 psql 中
\sf 函数名

-- 或 SQL 查询
SELECT pg_get_functiondef('函数名'::regproc);

✅ 3. 调试技巧

使用 RAISE NOTICE 输出调试信息：

RAISE NOTICE '当前值: %', variable_name;

在 psql 中设置：

SET client_min_messages = NOTICE;

🎯 实践任务

请完成以下操作：

创建一个函数 calc_bonus(salary NUMERIC)，返回奖金（工资的10%）
创建一个函数 get_top_employees(n INT)，返回工资最高的前 N 名员工（表结构自定）
创建一个存储过程 archive_old_orders(months INT)，将早于指定月数的订单移到 archive_orders 表
调用上述函数和过程，验证结果

✅ 总结

能力	函数（Function）	存储过程（Procedure）
封装逻辑	✅	✅
返回值	✅（必须）	❌（通过 OUT 参数）
事务控制	❌	✅
在 SQL 中直接调用	✅	❌
适用场景	查询、计算、转换	批量操作、ETL、维护