44. 自定义函数（FUNCTION）与 PL/pgSQL 入门

为什么写函数

函数（FUNCTION）把一段逻辑封起来，起个名字，以后一句调用就能复用。适合把复杂计算、固定查询封装好，避免到处复制 SQL。函数还能接收参数、返回结果，像一门小语言。

基本结构

postgres=# CREATE OR REPLACE FUNCTION 函数名(参数 类型, ...)
postgres=# RETURNS 返回类型
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# DECLARE
postgres=#   -- 变量声明
postgres=# BEGIN
postgres=#   -- 逻辑
postgres=#   RETURN 结果;
postgres=# END;
postgres=# $$;

$$ 是”美元引号”，用来包裹函数体，省得里面的单引号反复转义。如果体内也要用 $$，可以换成 $body$ 之类带标签的形式。

一个计数函数

统计某个年龄区间的用户数：

postgres=# CREATE OR REPLACE FUNCTION count_users_by_age(age_from INT, age_to INT)
postgres=# RETURNS INT
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# DECLARE
postgres=#   v_count INT;
postgres=# BEGIN
postgres=#   SELECT COUNT(*) INTO v_count
postgres=#   FROM users
postgres=#   WHERE age BETWEEN age_from AND age_to;
postgres=#   RETURN v_count;
postgres=# END;
postgres=# $$;

调用有两种写法：

postgres=# SELECT count_users_by_age(18, 30);            -- 位置传参
postgres=# SELECT count_users_by_age(age_from => 18, age_to => 30);  -- 命名传参

返回值：标量 / 表 / 集合

除了返回单个值，函数还能返回一整张表。用 RETURNS TABLE：

postgres=# CREATE OR REPLACE FUNCTION top_users(limit_n INT)
postgres=# RETURNS TABLE(username TEXT, order_cnt BIGINT)
postgres=# LANGUAGE sql
postgres=# AS $$
postgres=#   SELECT u.username, COUNT(o.id)
postgres=#   FROM users u
postgres=#   JOIN orders o ON o.user_id = u.id
postgres=#   GROUP BY u.username
postgres=#   ORDER BY COUNT(o.id) DESC
postgres=#   LIMIT limit_n;
postgres=# $$;

postgres=# SELECT * FROM top_users(5);

控制结构

PL/pgSQL 支持 IF、 CASE、循环等。先来个分档函数：

postgres=# CREATE OR REPLACE FUNCTION price_level(p NUMERIC)
postgres=# RETURNS TEXT
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   IF p < 10 THEN
postgres=#     RETURN '低价';
postgres=#   ELSIF p < 100 THEN
postgres=#     RETURN '中价';
postgres=#   ELSE
postgres=#     RETURN '高价';
postgres=#   END IF;
postgres=# END;
postgres=# $$;

循环示例，算 1 到 n 的累加：

postgres=# CREATE OR REPLACE FUNCTION sum_to(n INT)
postgres=# RETURNS INT
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# DECLARE
postgres=#   i INT := 1;
postgres=#   s INT := 0;
postgres=# BEGIN
postgres=#   WHILE i <= n LOOP
postgres=#     s := s + i;
postgres=#     i := i + 1;
postgres=#   END LOOP;
postgres=#   RETURN s;
postgres=# END;
postgres=# $$;

异常处理 EXCEPTION

函数里可以捕获异常，避免整个调用崩溃。比如做除法时防”除零”：

postgres=# CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
postgres=# RETURNS NUMERIC
postgres=# LANGUAGE plpgsql
postgres=# AS $$
postgres=# BEGIN
postgres=#   RETURN a / b;
postgres=# EXCEPTION
postgres=#   WHEN division_by_zero THEN
postgres=#     RETURN NULL;
postgres=# END;
postgres=# $$;

WHEN 后面可以写具体异常名（如 division_by_zero、 no_data_found），或笼统地用 WHEN others THEN 兜住所有异常。

函数属性：IMMUTABLE / STABLE / VOLATILE

函数可以标注行为，帮优化器做决定：

• IMMUTABLE：同样输入永远同样输出（如数学计算）。
• STABLE：一次查询内结果不变（如 now()）。
• VOLATILE：默认，每次都可能不同（如 random()）。

postgres=# CREATE FUNCTION add_tax(p NUMERIC) RETURNS NUMERIC
postgres=# LANGUAGE sql IMMUTABLE AS $$
postgres=#   SELECT p * 1.13;
postgres=# $$;

列出与删除函数

postgres=# \df                      -- 列出函数
postgres=# DROP FUNCTION IF EXISTS count_users_by_age(INT, INT);

带默认值的参数

参数可以带默认值，调用时省略就走默认。注意：有默认值的参数要放在参数列表后面。

postgres=# CREATE OR REPLACE FUNCTION greet(name TEXT, prefix TEXT DEFAULT 'Hello')
postgres=# RETURNS TEXT LANGUAGE plpgsql AS $$
postgres=# BEGIN
postgres=#   RETURN prefix || ', ' || name;
postgres=# END;
postgres=# $$;

postgres=# SELECT greet('alice');            -- Hello, alice
postgres=# SELECT greet('alice', 'Hi');      -- Hi, alice

PL/pgSQL 里返回多行：RETURN QUERY

返回集合的函数，在 PL/pgSQL 里用 RETURN QUERY 把查询结果逐批吐出来：

postgres=# CREATE OR REPLACE FUNCTION big_orders(min_amount NUMERIC)
postgres=# RETURNS TABLE(id INT, amount NUMERIC)
postgres=# LANGUAGE plpgsql AS $$
postgres=# BEGIN
postgres=#   RETURN QUERY
postgres=#     SELECT o.id, o.amount FROM orders o
postgres=#     WHERE o.amount >= min_amount;
postgres=# END;
postgres=# $$;

postgres=# SELECT * FROM big_orders(50);

函数安全：SECURITY DEFINER

默认函数以”调用者”的权限运行。加上 SECURITY DEFINER，函数会以”定义者”的权限运行，适合封装需要高权限的操作、又不给调用者直接权限的场景：

postgres=# CREATE FUNCTION ... SECURITY DEFINER ...

递归函数示例

函数可以调用自己。比如算阶乘：

postgres=# CREATE OR REPLACE FUNCTION factorial(n INT)
postgres=# RETURNS BIGINT LANGUAGE plpgsql AS $$
postgres=# BEGIN
postgres=#   IF n <= 1 THEN RETURN 1; END IF;
postgres=#   RETURN n * factorial(n - 1);
postgres=# END;
postgres=# $$;

动态 SQL：EXECUTE（简述）

需要运行时拼表名、列名时，用 EXECUTE 执行动态 SQL。务必用 format('%I', ...) 处理标识符，防注入：

postgres=# EXECUTE format('SELECT count(*) FROM %I', tab_name) INTO cnt;

函数该放在数据库还是应用里

这不是纯技术问题，更像一个权衡。把逻辑写进函数，好处是计算靠近数据，少搬运、易复用；坏处是数据库变重，不好做版本管理和单元测试。我的经验：简单的、和单条 SQL 强相关的计算（如格式化、校验、聚合）放函数很值；复杂业务流程、要调外部接口的逻辑，留在应用层更合适。

写函数时的性能注意

PL/pgSQL 不是越快越好。它在”每次执行都重新规划 SQL”这点上比纯 SQL 函数慢一截。如果逻辑只是拼一条固定查询，用 LANGUAGE sql 更轻。另外循环里别写查询，能改成集合操作（一条 SQL 搞定）就别用 FOR 逐行扫，性能能差几个数量级。

调试函数的小技巧

函数出错信息有时不够直观。调试时可以临时把中间变量用 RAISE NOTICE 'v=%', v_count; 打印出来看值；或者把函数体先拆成普通 SQL 在 psql 里一步步跑，确认逻辑对了再包回函数。复杂函数别一次写到底，分步验证最稳。

函数的返回类型别写错

定义函数时 RETURNS 后面写错类型，调用时就会类型不匹配报错。比如预期返回 INT 却写了 TEXT，外层查询做加法就会失败。写之前先想清楚”这个函数到底产出一个什么”。返回表用 RETURNS TABLE，返回集合用 SETOF，别混。返回多行又想带列名，优先 RETURNS TABLE，调用时直接 SELECT * FROM 函数() 最直观。

常见误区

1. 函数里用 RETURN 却忘了在 DECLARE 里声明变量类型，导致赋值失败。
2. 把 VOLATILE 函数误标 IMMUTABLE，查询结果出错。
3. 删除函数忘带参数类型，重载时删不干净。
4. 用 SELECT 函数() 调本该 CALL 的过程（见第 45 章）。