PostgreSQL Functions 函数实用教程
本教程以 PostgreSQL 18 为参考，介绍常用内置函数、用户自定义函数、PL/pgSQL、集合返回、函数属性及常见设计注意事项。
函数是什么
函数接收零个或多个参数，执行计算或数据库操作，并返回一个值或一组行。PostgreSQL 内置大量函数，也支持使用 SQL、PL/pgSQL 及其他受支持的语言定义函数。
| 类别 | 用途 | 调用形式示例 |
|---|---|---|
| 标量函数 | 每次调用返回一个值 | lower('HELLO') |
| 聚合函数 | 对多行数据汇总为一个结果 | count(*)、sum(amount) |
| 窗口函数 | 对查询窗口内的行进行计算，保留各行结果 | row_number() OVER (...) |
| 集合返回函数 | 一次返回多行，可放入 FROM 子句 | generate_series(1, 5) |
| 过程（Procedure） | 执行过程式操作，通过 CALL 调用，不用函数返回值表达结果 | CALL refresh_data() |
常见内置函数
下面是常用函数示例，完整函数清单可按功能类别查阅 PostgreSQL 文档。
-- 字符串
SELECT lower('PostgreSQL'), upper('hello');
SELECT length('database'), concat_ws('-', 'user', 42);
-- 数值
SELECT abs(-12), round(12.345, 2), trunc(12.9);
-- NULL 与条件表达式
SELECT coalesce(NULL, '备用值');
SELECT nullif(10, 10);
-- 日期时间
SELECT current_date, now();
SELECT date_trunc('month', now());
-- JSONB
SELECT '{"name":"Ada"}'::jsonb ->> 'name';
聚合函数、窗口函数和普通标量函数都可以出现在查询表达式中，但它们的计算规则不同。比如 count(*) 会聚合多行，row_number() 需要配合 OVER，而 lower(text) 是逐值计算。
创建 SQL 函数
SQL 函数适用于可以用 SQL 表达的逻辑。使用美元引用符（如 $$）包裹函数体，避免内部 SQL 字符串中的单引号转义。
RETURNS 返回类型
LANGUAGE SQL
AS $$
SQL 语句
$$;
CREATE FUNCTION add_tax(amount numeric, rate numeric) RETURNS numeric LANGUAGE SQL IMMUTABLE STRICT AS $$ SELECT amount * (1 + rate);
$$;
SELECT add_tax(100, 0.08);
参数可使用位置引用 $1、$2，也可使用参数名。函数名允许重载：同名函数可按不同参数类型区分；定义和删除时要留意参数类型签名。
-- 显式声明参数名称与类型签名
CREATE FUNCTION full_name(first_name text, last_name text) RETURNS text LANGUAGE SQL AS $$ SELECT concat_ws(' ', first_name, last_name);
$$;
SELECT full_name(last_name => 'Lovelace', first_name => 'Ada');
参数模式与默认值
输入参数是默认模式。OUT 和 INOUT 可用于输出多个值；提供默认值后，调用者可以省略末尾的可选输入参数。
CREATE FUNCTION rectangle_stats( width numeric, height numeric, OUT area numeric, OUT perimeter numeric ) LANGUAGE SQL AS $$ SELECT width * height, 2 * (width + height);
$$;
SELECT * FROM rectangle_stats(4, 3);
CREATE FUNCTION greet(name text, greeting text DEFAULT '你好') RETURNS text LANGUAGE SQL AS $$ SELECT greeting || '，' || name;
$$;
SELECT greet('小林');
当函数存在多个重载或带默认参数的签名时，调用可能产生歧义。设计时避免互相重叠的可选参数组合，并在必要时显式转换实参类型。
创建 PL/pgSQL 函数
PL/pgSQL 适合需要变量、条件、循环、异常处理或多步数据库操作的逻辑。函数体中的 SQL 语句通常以分号结束。
CREATE FUNCTION discounted_price( original_price numeric, discount_rate numeric ) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE result numeric;
BEGIN IF original_price IS NULL OR discount_rate IS NULL THEN RETURN NULL;
END IF;
IF original_price < 0 OR discount_rate < 0 OR discount_rate > 1 THEN RAISE EXCEPTION '价格或折扣比例超出允许范围';
END IF;
result := original_price * (1 - discount_rate);
RETURN round(result, 2);
END;
$$;
SELECT discounted_price(100, 0.15);
DECLARE 用于声明局部变量；IF/ELSIF/ELSE 用于条件分支；RETURN 返回结果。异常处理应只捕获预期错误，避免隐藏故障。
返回多行与表值函数
使用 RETURNS TABLE 声明输出列，并用 RETURN QUERY 返回一组行。表值函数常在 FROM 子句中调用。
CREATE FUNCTION orders_for_customer(target_customer_id bigint) RETURNS TABLE(order_id bigint, total numeric) LANGUAGE SQL STABLE AS $$ SELECT o.id, o.total FROM orders AS o WHERE o.customer_id = target_customer_id;
$$;
SELECT * FROM orders_for_customer(42);
PL/pgSQL 版本可用 RETURN QUERY 附加结果集，也可在循环中使用 RETURN NEXT 逐行返回。
CREATE FUNCTION positive_numbers(max_value integer) RETURNS SETOF integer LANGUAGE plpgsql IMMUTABLE STRICT AS $$ BEGIN RETURN QUERY SELECT n FROM generate_series(1, max_value) AS g(n);
END;
$$;
SELECT * FROM positive_numbers(5);
若表值函数需要引用同一查询中前面的表，可以使用 LATERAL；对函数调用而言，LATERAL 关键字通常可以省略，但写出它有助于表达相关关系。
SELECT c.id, o.order_id, o.total FROM customers AS c CROSS JOIN LATERAL orders_for_customer(c.id) AS o;
函数与过程的区别
| 对比项 | 函数（FUNCTION） | 过程（PROCEDURE） |
|---|---|---|
| 定义 | CREATE FUNCTION | CREATE PROCEDURE |
| 调用 | 通常在表达式中调用，如 SELECT f() | 使用 CALL p() |
| 返回值 | 必须声明返回类型，也可返回 void 或一组行 | 没有函数式返回值，可用输出参数传回数据 |
| 事务控制 | 不能在函数中执行 COMMIT、ROLLBACK 等事务控制命令 | 在符合调用上下文等限制时可进行事务控制 |
CREATE PROCEDURE remove_expired_sessions() LANGUAGE SQL AS $$ DELETE FROM sessions WHERE expires_at < now();
$$;
CALL remove_expired_sessions();
需要在 SQL 表达式或查询中计算并使用返回值时，选函数；需要执行过程式动作、且不需要函数式返回值时，可考虑过程。
Volatility：函数易变性
易变性是给查询优化器的行为承诺，不是性能开关。声明必须符合函数的真实行为。
| 属性 | 含义 | 典型例子 |
|---|---|---|
IMMUTABLE | 相同参数永远返回相同结果；不依赖表数据、当前时间或会话设置。 | 纯算术计算、固定规则的字符串转换。 |
STABLE | 同一条语句执行期间，对相同参数返回一致结果；不修改数据库。 | 读取表数据的查询函数、依赖当前语句快照的计算。 |
VOLATILE | 结果可能随调用变化或有副作用；这是默认分类。 | random()、nextval()、执行写操作的函数。 |
把读取表数据或依赖当前时间的函数错误标为 IMMUTABLE，可能导致常量折叠或索引结果与实际数据不一致。只有确认满足属性定义时，才使用更强的优化承诺。
STRICT、并行安全与其他属性
STRICT（也称RETURNS NULL ON NULL INPUT）：任何输入参数为 NULL 时，数据库不调用函数，直接返回 NULL。仅当该行为符合业务逻辑时使用。CALLED ON NULL INPUT：默认行为，即使参数为 NULL 也调用函数，让函数自行处理 NULL。PARALLEL SAFE、PARALLEL RESTRICTED、PARALLEL UNSAFE：描述函数在并行查询中的安全程度。用户定义函数默认是PARALLEL UNSAFE；只有确认实现满足条件后才声明更宽松属性。COST和ROWS：为优化器提供函数调用成本和集合返回行数估计；仅在有依据时调整。
CREATE FUNCTION normalize_code(value text) RETURNS text LANGUAGE SQL IMMUTABLE STRICT AS $$ SELECT upper(btrim(value));
$$;
动态 SQL 与参数
静态 SQL 应优先于动态 SQL。PL/pgSQL 的 EXECUTE 可在表名、列名等标识符需要动态变化时使用：用 format('%I', ...) 引用标识符，用 USING 绑定数据值。
CREATE FUNCTION count_rows_in_table(table_name text) RETURNS bigint LANGUAGE plpgsql STABLE AS $$ DECLARE row_count bigint;
BEGIN EXECUTE format('SELECT count(*) FROM %I', table_name) INTO row_count;
RETURN row_count;
END;
$$;
数据值不要通过字符串拼接放进 SQL。对值使用 USING 参数绑定；对标识符使用 %I。如果函数允许调用者选择表或 schema，还必须做授权白名单检 查，避免仅靠正确引用就误把任意对象开放给调用者。
-- 值使用 USING 传递，避免拼接 SQL 字面量 EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', table_name) INTO row_count USING 'active';
上例说明动态标识符的安全引用；真实业务中，通常还应将可访问对象限制到允许的 schema 和表。
SECURITY INVOKER 与 SECURITY DEFINER
默认 SECURITY INVOKER 以调用者权限执行。SECURITY DEFINER 以函数所有者权限执行，适合经过严格设计的受控权限入口，但配置不当会造成越权访问。
-- 默认行为：以调用者权限运行
CREATE FUNCTION app.current_user_orders() RETURNS TABLE(order_id bigint) LANGUAGE SQL SECURITY INVOKER AS $$ SELECT id FROM app.orders WHERE customer_id = current_user_id();
$$;
确需使用 SECURITY DEFINER 时，应由可信所有者创建，固定安全的 search_path（仅使用可信 schema，并把 pg_temp 放在最后），对对象使用 schema 限定名，限制参数和可访问对象，并审慎管理 EXECUTE 权限。
-- 示意：函数体应只执行经过授权和验证的固定操作
CREATE FUNCTION app.admin_operation() RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, app, pg_temp AS $$ BEGIN
-- 在此处实现经过审查的最小权限操作 NULL;
END;
$$;
REVOKE ALL ON FUNCTION app.admin_operation() FROM PUBLIC;
SECURITY DEFINER 示例只是配置模式，不代表仅设置 search_path 就足够安全。还要检查函数逻辑、对象权限、默认公开的执行权限，以及所有被调用对象的 schema 限定。
查看、替换与删除函数
函数签名由名称和输入参数类型组成；删除重载函数时，需要指定正确的输入参数类型。修改参数类型或返回类型通常需要删除并重建函数。
-- 查看当前 schema 中的函数
SELECT n.nspname AS schema_name, p.proname AS function_name, pg_get_function_identity_arguments(p.oid) AS arguments, pg_get_function_result(p.oid) AS result_type FROM pg_proc AS p JOIN pg_namespace AS n ON n.oid = p.pronamespace WHERE n.nspname = current_schema() ORDER BY p.proname;
-- 仅在签名一致时替换函数体
CREATE OR REPLACE FUNCTION add_tax(amount numeric, rate numeric) RETURNS numeric LANGUAGE SQL IMMUTABLE STRICT AS $$ SELECT amount * (1 + rate);
$$;
-- 删除指定重载
DROP FUNCTION IF EXISTS add_tax(numeric, numeric);
CREATE OR REPLACE FUNCTION 可以替换兼容签名的函数定义，但不能用来改变现有函数的返回类型。删除函数前，先检查依赖对象；避免在生产环境不加评估地使用 CASCADE。
常见错误与实践建议
- **返回类型不匹配：**检查 SQL 函数最后一条语句的列数、顺序和类型；复合返回类型尤其要匹配列顺序。
- **函数解析不明确：**重载、默认参数和未知类型字面量可能造成歧义；使用 schema 限定或显式类型转换。
- **把函数当作过程调用：**函数用在
SELECT等表达式中；过程使用CALL。 - **错误标注属性：**不要为了“让查询更快”而随意标记
IMMUTABLE或PARALLEL SAFE。 - **误用集合返回函数：**明确函数返回零行、一行还是多行；查询关联参数时考虑
LATERAL。 - **忽略 NULL：**确认函数遇到 NULL 的业务语义，再选择
STRICT或自行处理。 - **滥用大型函数：**函数有助于封装和复用，但过长的过程式逻辑不易测试和优化；把复杂业务拆分为清晰、可验证的步骤。