CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; 定义一个函数,查询可以像调用内置函数一样调用它。函数体写在两个 $$ 之间,可以用普通 SQL,也可以用 PostgreSQL 的过程语言 PL/pgSQL。CREATE PROCEDURE(PostgreSQL 11+)定义的则是用 CALL 运行的例程。
with_tax(50) 使用默认税率,返回 60;with_tax(50, 0.1) 返回 55。参数也可以按名字传递:with_tax(50, rate => 0.1)。
用 PL/pgSQL 编写逻辑
当函数需要变量、IF、循环或错误处理时,用 LANGUAGE plpgsql。变量写在 DECLARE 部分,代码写在 BEGIN 和 END 之间:
4 kg 的包裹只收基础运费 5;20 kg 的加急包裹运费是 (5 + 10 * 0.5) * 2 = 20。在 PL/pgSQL 中,RAISE NOTICE 'value: %', cost; 打印一条消息,RAISE EXCEPTION 则报错并中止。
如果普通 SQL 就能表达逻辑,优先用 LANGUAGE sql:查询规划器可以把简单的 SQL 函数内联到调用它的查询中。
返回多行
RETURNS TABLE (...) 让函数返回多行。像表一样在 FROM 中调用它:
orders_of('ana') 返回订单 1 和 3。RETURNS SETOF orders 则返回 orders 表的整行,而不是列出的那几列。
从 PostgreSQL 14 开始,SQL 函数的函数体可以用标准 SQL 写,而不是写成字符串。PostgreSQL 会以解析后的形式存储它,并记录它用到的表和列,所以只要函数还在,这些表和列就不能被删除:
CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
SELECT count(*) FROM orders WHERE customer = who;
END;
创建存储过程并用 CALL 运行
存储过程没有返回类型,用 CALL 运行。它可以修改数据,而且当 CALL 不在显式事务块中时,它可以在中途 COMMIT。大任务就是这样分批处理的。
账户 1 最后剩 70,账户 2 变成 80。如果转账 200,就会违反账户 1 上的 CHECK 约束,这个错误会把两次更新都回滚。
| 函数 | 存储过程 | |
|---|---|---|
| 运行方式 | SELECT f(...),可以出现在查询中的任何位置 | 只能用 CALL p(...) |
| 返回 | 一个值、一行或多行 | 不返回(或通过 OUT/INOUT 参数) |
内部 COMMIT / ROLLBACK | 不能 | 能,在 PL/pgSQL 中,且不在事务块中调用时 |
| 从哪个版本开始 | 一直都有 | PostgreSQL 11 |
标注易变性:IMMUTABLE、STABLE、VOLATILE
易变性标签告诉查询规划器可以对函数做哪些假设。默认是 VOLATILE。
IMMUTABLE:结果只取决于参数,永远不变(with_tax)。用在索引表达式中的函数必须是这一类。STABLE:结果在不同语句之间可能变化,但在同一条语句中不变,比如读取表的函数(orders_of)。VOLATILE:其他所有情况,比如使用random()或修改数据的函数。PostgreSQL 会为每一行重新调用它。
标签写错会得到错误的结果:读取表的 IMMUTABLE 函数可能从索引或缓存的计划中返回过时的值。
替换、重载和删除函数
CREATE OR REPLACE FUNCTION 可以修改已有函数的函数体,但不能修改返回类型;要改返回类型,得先删除函数。PostgreSQL 用名字和参数类型来识别函数,所以 f(int) 和 f(text) 是两个函数。要删除两个同名函数中的一个,需要写出参数类型:
CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;
PL/pgSQL 章节介绍了循环、游标、异常以及用 EXECUTE 执行的动态 SQL。要在行发生变化时自动运行函数,把它挂到触发器上。
常见问题
PostgreSQL 中函数和存储过程有什么区别?
函数返回一个值,在查询中调用(SELECT f(x))。存储过程是 PostgreSQL 11 新增的,用 CALL 单独运行,可以在过程体中执行 COMMIT 或 ROLLBACK;而函数总是在调用方的事务中运行。
PostgreSQL 函数中的 $$ 是什么意思?
$$ 是美元符号引用:它标记一个字符串字面量的开始和结束,这样函数体中的单引号就不需要转义。你也可以加上标签,比如 $body$ ... $body$。
怎么查看 PostgreSQL 函数的代码?
在 psql 中,\df 列出函数,\sf name 打印函数的定义。在 SQL 中,SELECT pg_get_functiondef('name'::regproc); 返回完整的 CREATE OR REPLACE FUNCTION 语句。
PostgreSQL 函数能返回多行吗?
能。把它声明为 RETURNS TABLE (col type, ...) 或 RETURNS SETOF type,然后像表一样在 FROM 子句中调用:SELECT * FROM f(1);。