postgresql存储过程如何写
在 PostgreSQL 里，其实 99% 场景你写的是 函数（FUNCTION）；
真正的 存储过程（PROCEDURE） 是 11 版才引入，语法几乎一样，只是调用方式不同。
下面把“函数”和“过程”都给你一次讲透，并附完整可直接 `psql -f` 运行的脚本。
---
一、先分清：FUNCTION vs PROCEDURE
特性 FUNCTION PROCEDURE
最早版本 一直就有 ≥ 11
能否回传结果集 ✅ `RETURNS TABLE / SETOF` ❌（只能 OUT 参数）
调用方式 `SELECT func(args);` `CALL proc(args);`
能否无返回值 ❌ 必须有 `RETURNS` ✅ 可以啥都不返回
事务控制 由外层决定 内部可 `COMMIT/ROLLBACK`
结论：
- 只要你想“查”数据 → 写 FUNCTION；
- 只要你想“干”事且内部自己提交 → 写 PROCEDURE。
---
二、FUNCTION 模板（最常用）
```sql
-- 返回单行
CREATE OR REPLACE FUNCTION get_user_name(uid bigint)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
v_name text;
BEGIN
SELECT name INTO v_name
FROM users
WHERE id = uid;
RETURN v_name; -- 找不到会返回 NULL
END;
$$;
-- 返回结果集
CREATE OR REPLACE FUNCTION list_users(min_age int)
RETURNS TABLE(id bigint, name text, age int)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT u.id, u.name, u.age
FROM users u
WHERE u.age >= min_age
ORDER BY u.age;
END;
$$;
-- 测试
SELECT get_user_name(1);
SELECT * FROM list_users(18);
```
---
三、PROCEDURE 模板（≥ pg11）
```sql
-- 内部自己提交，外部自动回滚不了
CREATE OR REPLACE PROCEDURE transfer_money(
from_id bigint,
to_id bigint,
amount numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
-- 扣钱
UPDATE accounts SET balance = balance - amount WHERE id = from_id;
-- 加钱
UPDATE accounts SET balance = balance + amount WHERE id = to_id;
-- 记日志
INSERT INTO log(from_acc, to_acc, amt, ts)
VALUES (from_id, to_id, amount, now());
-- 手动提交（函数里不允许）
COMMIT;
END;
$$;
-- 调用
CALL transfer_money(3, 5, 100.00);
```
---
四、参数 5 种写法速查
1. `IN` 默认，只进不出
2. `OUT` 只出不进
3. `INOUT` 能进能出
4. `VARIADIC` 可变长参数
5. 缺省值 `age int DEFAULT 18`
示例（INOUT）：
```sql
CREATE OR REPLACE FUNCTION swap(INOUT a int, INOUT b int)
LANGUAGE plpgsql AS $$
DECLARE tmp int;
BEGIN
tmp := a; a := b; b := tmp;
END;
$$;
-- 调用
SELECT swap(3,7); -- 返回 7,3
```
---
五、异常处理模板
```sql
CREATE OR REPLACE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE '除零，返回 NULL';
RETURN NULL;
WHEN OTHERS THEN
RAISE WARNING '未知错误: %', SQLERRM;
RETURN NULL;
END;
$$;
```
---
六、快速验证脚本
把下面内容存成 `proc.sql`，然后
`psql -U postgres -d yourdb -f proc.sql` 即可一键验证。
```sql
-- 建测试表
DROP TABLE IF EXISTS users;
CREATE TABLE users(
id bigserial PRIMARY KEY,
name text NOT NULL,
age int CHECK (age>0)
);
-- 灌点数据
INSERT INTO users(name,age) VALUES
('Alice',19),('Bob',25),('Carol',30);
-- 函数：返回结果集
CREATE OR REPLACE FUNCTION list_users(min_age int)
RETURNS TABLE(id bigint, name text, age int)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT u.id, u.name, u.age FROM users u WHERE u.age >= min_age;
END;
$$;
-- 过程：无结果，内部可 COMMIT
CREATE OR REPLACE PROCEDURE add_user(p_name text, p_age int)
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO users(name,age) VALUES (p_name, p_age);
COMMIT;
END;
$$;
-- 测试
SELECT * FROM list_users(20); -- 函数
CALL add_user('David', 40); -- 过程
SELECT * FROM list_users(20);
```
---
七、小结口诀
- 要查询 → `FUNCTION` + `RETURNS`/`TABLE`
- 要干活 → `PROCEDURE` + `CALL`（≥11）
- 想回传单个值 → `OUT` 参数
- 想回传结果集 → `RETURN QUERY`