PHP小码农

Appearance

CREATE FUNCTION 基础 ​

CREATE FUNCTION 是把一段 SQL 或过程化代码登记进系统目录、让它可以像内置函数一样被调用的语句。本节从语句本身出发：讲清完整语法骨架、LANGUAGE 子句的四种取值、美元引用为什么优于单引号、RETURN 与 RETURN QUERY 的分工，并把 CREATE OR REPLACE 那条最容易被忽视的限制——改返回类型或参数名必须 DROP 重建——单独强调。学完之后，你能独立写出可上线、可维护、权限受控的自定义函数。

与「内置函数」的分工

前面几个阶段的章节都在讲 PostgreSQL 自带的函数（字符串、日期、数组、JSON、序列、UUID）；从本节起进入自定义函数（User-Defined Function）。内置函数由 C 语言实现在数据库二进制里，自定义函数则是你写进系统目录 pg_proc 的一段可执行代码。两者的调用方式完全一致，但生命周期、性能特征、权限模型完全不同——这正是本节要建立的心智模型。

前置知识

阅读本节前，建议先了解：UUID 生成函数

基础概念 ​

函数（Function）是 PostgreSQL 中被调用的可执行单元。调用点可以出现在 SELECT 列表、WHERE、ORDER BY、CHECK 约束、列默认值、生成列、触发器以及索引表达式中。它在系统目录里由名称 + 输入参数类型序列共同标识，这称为签名（Signature）。

概念术语说明
函数Function登记在 pg_proc 中、可被 SQL 语句调用的可执行单元
函数头Function Header名称、参数、RETURNS、LANGUAGE、易变性、安全属性等声明部分
函数体Function BodyAS 后面那段字符串，含义由语言处理器决定
签名Signature名称 + 输入参数类型序列，是函数在 pg_proc 中的唯一键
重载Overloading同名但参数类型序列不同的多个函数共存
语言处理器Language Handler解释函数体的模块：sql / plpgsql / c / internal
美元引用Dollar Quoting用 $tag$ ... $tag$ 包裹字面量，内部单引号无需转义
内联Inlining优化器把 sql 函数的函数体展开进调用它的查询
易变性VolatilityIMMUTABLE / STABLE / VOLATILE，决定优化器能做的假设
安全上下文Security ContextSECURITY INVOKER（默认）与 SECURITY DEFINER

函数由两部分组成，理解这个划分是理解后续所有细节的前提：

组成部分内容例子
头（Header）名称、参数、返回类型、语言、易变性、安全属性CREATE FUNCTION f(i int) RETURNS int LANGUAGE sql
体（Body）真正执行的代码，写在美元引用里AS $$ SELECT i + 1 $$

LANGUAGE 声明函数体用什么语言处理器（Language Handler）解释。PostgreSQL 内置了四种，用户最常用前两种：

语言函数体是什么能否内联有无控制流适用场景
sql一条或多条 SQL 语句（末条决定返回值）能无简单映射、查询封装、性能敏感路径
plpgsql语句序列（BEGIN ... END）不能有业务逻辑、多步写入、异常处理
c.so 动态库 + 链接符号不能有极致性能、系统集成（需编译）
internal只能用内核已有的函数名——数据库内部使用，普通用户无法新建

先记住这条：语言选择决定了函数能不能被优化器「看穿」，而这一点的性能影响常常是数量级。sql 与 plpgsql 的完整对比见 SQL 函数 vs PL/pgSQL 函数。

语法与用法 ​

text
CREATE [ OR REPLACE ] FUNCTION 名称 ( [ 参数模式 ] [ 参数名 ] 参数类型 [ DEFAULT 默认值 ] [, ...] )
  [ RETURNS 返回类型 | RETURNS TABLE ( 列名 列类型 [, ...] ) ]
  LANGUAGE 语言名
  [ IMMUTABLE | STABLE | VOLATILE ]                         -- 易变性，默认 VOLATILE
  [ STRICT | CALLED ON NULL INPUT ]                         -- NULL 入参策略，默认 CALLED ON NULL INPUT
  [ SECURITY INVOKER | SECURITY DEFINER ]                   -- 安全上下文，默认 INVOKER
  [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]               -- 并行安全性，默认 UNSAFE
  [ COST 执行代价 ] [ ROWS 预估行数 ]                        -- 规划器提示
  [ SET 配置参数 { TO | = } 值 ] [ ... ]
  [ AS 函数体 ]

-- PostgreSQL 14+ 的 SQL 标准函数体写法（语言默认 sql，且必须放最后）
CREATE FUNCTION 名称 ( 参数... ) RETURNS 类型 LANGUAGE sql RETURN 表达式;

最小可运行示例——同一个「加一」逻辑的两种语言实现：

sql
-- 1) LANGUAGE sql：函数体是一条 SELECT 表达式
CREATE FUNCTION demo_add_one(i integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT i + 1;
$$;
sql
-- 2) LANGUAGE plpgsql：函数体是语句序列，必须显式 RETURN
CREATE FUNCTION demo_grade_of(score integer)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF score >= 90 THEN
    RETURN 'A';
  ELSIF score >= 60 THEN
    RETURN 'PASS';
  ELSE
    RETURN 'FAIL';
  END IF;
END;
$$;
sql
SELECT demo_add_one(41) AS plus_one,
       demo_grade_of(95) AS grade_a,
       demo_grade_of(30) AS grade_fail;
text
 plus_one | grade_a | grade_fail
----------+---------+------------
       42 | A       | FAIL
(1 row)

详细说明 ​

函数头与函数体：为什么 AS 后面只是一段字符串 ​

CREATE FUNCTION 在创建时只做两件事：解析函数头（名称、参数、返回类型、属性），把 AS 后面的内容原样存成 pg_proc.prosrc。函数体的语法通常不在创建时校验——plpgsql 的函数体到第一次执行才编译。

sql
-- 这段代码创建时不会报错，语法错误藏在字符串里
CREATE FUNCTION demo_broken() RETURNS integer
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN 1 +;   -- 故意的语法错误
END;
$$;
text
CREATE FUNCTION
sql
SELECT demo_broken();
text
ERROR:  syntax error at or near ";"
LINE 3:   RETURN 1 +;
                   ^

结论：写完函数立即调用一次，不要等应用上线才发现拼写错误。这也是「创建脚本末尾附带一个 SELECT f() 自检」这条工程习惯的由来。

LANGUAGE 子句：sql / plpgsql / c / internal ​

sql
-- 查看数据库里已安装的语言处理器（plpgsql 默认安装，其他语言需装扩展）
SELECT lanname, lanpltrusted AS trusted, lanplcallfoid::regproc AS handler
FROM pg_language
ORDER BY lanname;
text
  lanname  | trusted |  handler
-----------+---------+----------------
 c         | f       | -
 internal  | f       | -
 plpgsql   | t       | plpgsql_call_handler
 sql       | t       | -
(4 rows)

几个容易忽略的事实：

  • sql 与 c / internal 不是「过程化语言（PL）」，它们内置在内核，不出现在 \dx 扩展列表中。
  • plpgsql 是默认安装的扩展，但它仍然是扩展，用 \dx 能看到：
sql
-- psql 元命令：查看已安装扩展
\dx plpgsql
text
                  List of installed extensions
   Name   | Version | Schema |            Description
----------+---------+--------+------------------------------------
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

PL/pgSQL 本身的 MAJOR / MINOR 版本号与 PostgreSQL 主版本绑定，plpgsql.extra_warnings、plpgsql.check_asserts 等 GUC 参数可以直接在会话里设置：

sql
-- 打开「可疑赋值」与「常量条件」告警，帮助发现 plpgsql 里的低级错误
SET plpgsql.extra_warnings = 'all';
  • c 与 internal 不能用于生产环境的普通业务逻辑。c 需要编译动态库并具备服务器文件系统访问能力；internal 只允许注册内核已有的符号，且仅超级用户能创建。
  • 不写 LANGUAGE 时默认是 sql，但那是 PostgreSQL 14+ 的 SQL 标准函数体写法（RETURN 表达式）才有的默认；用 AS $$ ... $$ 形式的传统函数必须显式声明。
sql
-- PostgreSQL 14+：SQL 标准函数体，语言默认 sql，写法更紧凑
CREATE FUNCTION demo_add_one_v2(i integer) RETURNS integer
RETURN i + 1;
sql
-- PostgreSQL 14+：过程也可以用 BEGIN ATOMIC（函数不支持）
CREATE PROCEDURE demo_cleanup()
LANGUAGE sql
BEGIN ATOMIC
  DELETE FROM demo.orders WHERE status = 'cancelled';
END;

美元引用（Dollar Quoting）：为什么几乎是强制用法 ​

函数体是字符串常量。如果用普通单引号包裹，函数体内每一个单引号都要写成两个，反斜杠也要转义：

sql
-- 不推荐：单引号包裹，内部引号翻倍，几乎无法维护
CREATE FUNCTION demo_bad() RETURNS text LANGUAGE sql
AS 'SELECT ''hello''::text || '' world''::text';
sql
-- 推荐：美元引用，内部引号原样写
CREATE FUNCTION demo_good() RETURNS text LANGUAGE sql
AS $$ SELECT 'hello'::text || ' world'::text AS greeting $$;

SELECT demo_good();
text
   demo_good
----------------
 hello world
(1 row)

嵌套时给外层换一个标签。当函数体内部本身包含 $$（比如要在函数里再 EXECUTE 一段 CREATE FUNCTION），内层就用不同的标签：

sql
CREATE FUNCTION demo_outer() RETURNS text
LANGUAGE plpgsql
AS $outer$
DECLARE
  v_ddl text;
BEGIN
  v_ddl := $inner$
    SELECT '生成于内层'::text;
  $inner$;
  RETURN v_ddl;
END;
$outer$;

标签的语法要求只有三条：以 $ 开头、以 $ 结尾、中间是合法标识符字符。常用 $body$、$fn$、$q$。

内外标签必须不同

如果内外都用 $$，解析器会在内层的第一个 $$ 处提前闭合字符串，随后报出一串看似无关的语法错误。只要函数体里出现 $$，外层就必须换标签。

AS 后面也可以写两个字符串（AS 'obj_file', 'link_symbol'），那是 C 语言函数的形态，普通业务函数用不到。

函数体基本结构：sql 与 plpgsql 的分野 ​

维度LANGUAGE sqlLANGUAGE plpgsql
体结构一条或多条 SQL 语句，分号分隔DECLARE ... BEGIN ... END; 块
返回值来源最后一条 SELECT / INSERT ... RETURNING / UPDATE ... RETURNING显式 RETURN / RETURN QUERY / RETURN NEXT
变量没有（只能用 $1、$2 或参数名）有 DECLARE 段
控制流没有IF / CASE / LOOP / FOR / WHILE
异常处理没有BEGIN ... EXCEPTION ... END
动态 SQL没有EXECUTE / RETURN QUERY EXECUTE
能否内联满足条件即可永远不能

LANGUAGE sql 函数体里不能写 BEGIN ... END——那是 plpgsql（及 PostgreSQL 14+ 的 BEGIN ATOMIC）的语法：

sql
-- 错误！sql 语言里没有 BEGIN ... END 这个概念
CREATE FUNCTION demo_wrong() RETURNS integer
LANGUAGE sql
AS $$
BEGIN
  SELECT 1;
END;
$$;
text
ERROR:  syntax error at or near "BEGIN"
LINE 3: BEGIN
        ^

sql 语言也不能有多条「各自独立」的语句影响返回值，但体里可以有界于 WITH 的条件逻辑；需要真正流程控制时请改用 plpgsql：

sql
-- sql 语言里的条件逻辑只能靠 CASE / COALESCE / NULLIF
CREATE FUNCTION demo_grade_sql(score integer) RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 60 THEN 'PASS'
           ELSE 'FAIL'
         END;
$$;

RETURNS 与 RETURN：声明与返回是两件事 ​

RETURNS（带 S）属于函数头，声明返回类型；RETURN（不带 S）属于函数体，产生返回值。这是初学者最常混淆的一对关键字。

写法所属位置作用
RETURNS integer函数头声明：本函数返回一个整数
RETURN 42;plpgsql 函数体执行：返回 42 并结束函数
RETURN;plpgsql void 函数体提前结束，无返回值
RETURN QUERY SELECT ...plpgsql 函数体把查询结果整体追加进结果集

RETURNS 的常见形态：

写法含义调用方式
RETURNS void不返回值，只为副作用SELECT f();
RETURNS integer单个标量SELECT f();
RETURNS integer[]数组SELECT f(); / unnest(f())
RETURNS SETOF integer多行单列SELECT * FROM f();
RETURNS SETOF users多行，行结构取自表 usersSELECT * FROM f();
RETURNS TABLE(a int, b text)多行多列SELECT * FROM f();
RETURNS record结构未定型，需 OUT 或调用处给列定义SELECT * FROM f() AS t(...)
RETURNS refcursor返回游标名事务内配合 FETCH

完整的返回类型清单、OUT 参数与 TABLE 的等价关系、RETURN NEXT / RETURN QUERY EXECUTE 的用法，全部放在 函数参数与返回类型 一节展开。

RETURN vs RETURN QUERY ​

RETURN 返回一个值（或一整行）；RETURN QUERY 把一条查询的全部结果追加到函数的结果集，用于 RETURNS SETOF / RETURNS TABLE / RETURNS void。

sql
-- RETURN：一次调用只产出一个值，函数随即结束
CREATE FUNCTION demo_first_even(n integer) RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN n + (n % 2);        -- 找到后立即结束
END;
$$;
sql
-- RETURN QUERY：把整条查询的结果集作为函数结果
CREATE FUNCTION demo_evens(n integer) RETURNS SETOF integer
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT g FROM generate_series(2, n, 2) AS g;
END;
$$;

SELECT array_agg(x) AS evens FROM demo_evens(10) AS x;
text
      evens
------------------
 {2,4,6,8,10}
(1 row)

RETURN QUERY 与 RETURN NEXT 的区别是「一次追加一整集」还是「一行一行追加」：

写法追加单位典型场景
RETURN QUERY SELECT ...一条查询的全部行结果就是一条查询
RETURN NEXT 表达式一行（循环内反复调用）需要边算边吐、逐行加工
RETURN QUERY EXECUTE '动态 SQL'动态查询的全部行表名/列名由参数决定
sql
-- RETURN NEXT：循环里逐行追加
CREATE FUNCTION demo_fib(n integer) RETURNS SETOF bigint
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  a bigint := 0;
  b bigint := 1;
  t bigint;
  i integer;
BEGIN
  FOR i IN 1..n LOOP
    RETURN NEXT a;           -- 每次追加一行
    t := a + b;
    a := b;
    b := t;
  END LOOP;
END;
$$;

SELECT array_agg(x) AS fib10 FROM demo_fib(10) AS x;
text
          fib10
---------------------------
 {0,1,1,2,3,5,8,13,21,34}
(1 row)

LANGUAGE sql 里没有 RETURN

sql 函数的返回值来自最后一条语句：SELECT 或带 RETURNING 的 INSERT / UPDATE / DELETE / MERGE。写 RETURN 直接语法错误。需要 RETURN / RETURN QUERY 就必须用 plpgsql。

CREATE OR REPLACE 的三条限制（本节最重要的实践要点） ​

OR REPLACE 看起来很顺手，但它只允许改「函数体 + 辅助属性」，不允许改「标识函数身份的三要素」：

想改的东西CREATE OR REPLACE 允许吗后果
函数体（AS $$ ... $$）✅ 允许正常替换
易变性（IMMUTABLE ↔ VOLATILE）✅ 允许正常替换
SECURITY INVOKER ↔ DEFINER✅ 允许正常替换
SET search_path✅ 允许正常替换
COST / ROWS / PARALLEL✅ 允许正常替换
返回类型（RETURNS）❌ 不允许报错 cannot change return type of existing function
参数名（argname）❌ 不允许报错 cannot change name of input parameter
参数类型序列❌ 不算替换等于新建另一个函数，旧的仍在
sql
-- 创建 v1
CREATE FUNCTION demo_release(p_items integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT p_items * 2 $$;
sql
-- 错误！想改返回类型为 text，OR REPLACE 直接拒绝
CREATE OR REPLACE FUNCTION demo_release(p_items integer) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$ SELECT 'ok'::text $$;
text
ERROR:  cannot change return type of existing function
HINT:  Use DROP FUNCTION demo_release(integer) first.
sql
-- 错误！想改参数名也会被拒绝
CREATE OR REPLACE FUNCTION demo_release(p_count integer) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT p_count * 2 $$;
text
ERROR:  cannot change name of input parameter "p_items"
HINT:  Use DROP FUNCTION first.

改返回类型 / 参数名必须 DROP 重建

CREATE OR REPLACE 不能改返回类型，也不能改参数名。这不是限制的疏漏，而是刻意的：数据库里可能有视图、生成列、表达式索引、触发器、CHECK 约束依赖这个函数，改掉返回结构会让它们全部失效。

因此，当你要改的是返回类型或参数名时，正确的流程是：

sql
DROP FUNCTION IF EXISTS demo_release(integer);
CREATE FUNCTION demo_release(p_count integer) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$ SELECT p_count::text $$;

DROP 遇到依赖对象会立刻报错，从而强制你逐个检查依赖。反之，用 OR REPLACE 悄悄改掉行为的函数，可能让表达式索引里固化着旧算法的值，造成查询结果永久错乱。

同一个「改参数类型」的动作，OR REPLACE 不会报错，但结果更糟——它悄悄留下两个函数：

sql
CREATE OR REPLACE FUNCTION demo_release(p_items bigint) RETURNS integer
LANGUAGE sql IMMUTABLE
AS $$ SELECT p_items::integer * 3 $$;

-- 现在库里有两个 demo_release，重载解析会在调用时决定用哪个
\df demo_release
text
                 List of functions
 Schema |     Name      | Result data type | Argument data types
--------+---------------+------------------+---------------------
 public | demo_release  | integer          | p_items bigint
 public | demo_release  | integer          | p_items integer
(2 rows)

参数类型属于签名，所以改它 = 新建函数。重载带来的连锁问题见 函数重载与可变参数。

查看函数：\df / \sf / pg_proc ​

sql
-- psql 元命令
\df demo_*          -- 列出匹配名字的函数及其参数、返回类型
\df+ demo_release   -- 追加易变性、安全属性、语言、所有者、注释
\sf demo_release    -- 打印函数源码（source）
sql
-- 目录视图：函数在 pg_proc 中的关键字段
SELECT p.oid,
       p.proname                          AS name,
       pg_get_function_arguments(p.oid)   AS arguments,
       pg_get_function_result(p.oid)      AS result,
       l.lanname                          AS language,
       p.provolatile                      AS volatility,   -- i / s / v
       p.prosecdef                        AS security_definer,
       p.proisstrict                      AS strict,
       p.proowner::regrole                AS owner
FROM pg_proc AS p
JOIN pg_language AS l ON l.oid = p.prolang
WHERE p.proname = 'demo_release';
text
 oid  |    name      |        arguments         | result  | language | volatility | security_definer | strict | owner
------+--------------+--------------------------+---------+----------+------------+------------------+--------+-------
 16523| demo_release | p_items integer          | integer | sql      | i          | f                | f      | ch
 16524| demo_release | p_items bigint           | integer | sql      | i          | f                | f      | ch
(2 rows)

provolatile 的取值：i = IMMUTABLE、s = STABLE、v = VOLATILE。不写易变性就是 v，这是性能问题的常见源头，详见 IMMUTABLE / STABLE / VOLATILE。

导出可重建的 DDL，做版本管理时用这个而不是手抄：

sql
SELECT pg_get_functiondef('demo_release(integer)'::regprocedure) AS definition;
text
CREATE OR REPLACE FUNCTION public.demo_release(p_items integer)
 RETURNS integer
 LANGUAGE sql
 IMMUTABLE
AS $function$
 SELECT p_items * 2
$function$

注意它不包含 COMMENT ON、ALTER ... OWNER TO、GRANT / REVOKE，这些要单独导出。

删除函数：DROP FUNCTION 必须带参数类型 ​

函数靠签名标识，删除时也必须给签名：

sql
-- 错误！同名函数有多个时无法确定删哪个
DROP FUNCTION demo_release;
text
ERROR:  function name "demo_release" is not unique
HINT:  Specify the argument list to select the function unambiguously.
sql
-- 正确：带参数类型（重载场景下这是唯一可行写法）
DROP FUNCTION demo_release(integer);

-- 用 regprocedure 转换可避免类型别名带来的歧义
DROP FUNCTION 'demo_release(bigint)'::regprocedure;

DROP FUNCTION IF EXISTS f(参数类型); 是可重复执行脚本的标准开头；若确认要连带删除依赖对象，才使用 CASCADE——但生产环境请先看清楚它要级联删掉什么。

函数（Function）与存储过程（Procedure）的区别 ​

PostgreSQL 11 才引入存储过程，两者在调用方式和事务能力上差异明显：

维度函数（CREATE FUNCTION）存储过程（CREATE PROCEDURE）
调用方式SELECT f(...) / 出现在表达式中CALL p(...)，不能出现在表达式中
返回值必须有 RETURNS（可为 void），可返回集合没有返回值，只能靠 INOUT 参数带回
事务控制不能 COMMIT / ROLLBACK（在调用者事务内）可以 COMMIT / ROLLBACK / SET TRANSACTION
能否用于 FROM / 索引 / 生成列能不能
能否内联LANGUAGE sql 可被内联不适用
适合场景查询封装、表达式、表函数批量作业、分批提交、DDL 编排
sql
-- 过程：分批删除，每批提交，避免一个巨事务锁住整张表
CREATE PROCEDURE demo_purge_orders(p_batch integer DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
  v_deleted integer;
BEGIN
  LOOP
    DELETE FROM demo.orders
    WHERE id IN (
      SELECT id FROM demo.orders WHERE status = 'cancelled' LIMIT p_batch
    );
    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    EXIT WHEN v_deleted = 0;
    COMMIT;                     -- 只有过程能这样写
  END LOOP;
END;
$$;

CALL demo_purge_orders(100);

判断标准：需要在查询里用（SELECT 列表、WHERE、表函数）就用函数；需要跨事务边界做批量操作才用过程。业务代码里 90% 的场景是函数。

执行权限：GRANT EXECUTE 与默认的 PUBLIC ​

新函数创建后，EXECUTE 权限默认授予 PUBLIC——任何能连上该库的角色都能调用它。对只读辅助函数无害，但对会写数据、或 SECURITY DEFINER 的函数是隐患。

sql
-- 先收回默认权限，再按需授权
REVOKE ALL ON FUNCTION demo_add_one(integer) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION demo_add_one(integer) TO app_role;

更彻底的是从源头改默认权限，让以后新建的函数不再自动开放：

sql
-- 当前角色在 public schema 下新建的函数，默认不再对 PUBLIC 开放
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

查看现有权限分配：

sql
SELECT p.proname,
       pg_get_function_identity_arguments(p.oid) AS args,
       p.proacl
FROM pg_proc AS p
WHERE p.proname = 'demo_add_one';
text
   proname    |  args   |              proacl
--------------+---------+-----------------------------------
 demo_add_one | integer | {=X/ch,ch=X/ch,app_role=X/ch}
(1 row)

proacl 里 =X/ch 表示「PUBLIC 有执行权限、授予者是 ch」；REVOKE ... FROM PUBLIC 之后这一段会消失。CREATE 函数还需要参数类型与返回类型的 USAGE 权限，这是容易忽略的隐藏前提。

实战示例 ​

下面从零建一个演示 schema、四组函数，逐一验证行为、权限与内联效果，最后清理。

第一步：建 schema 与表，造数据

sql
CREATE SCHEMA IF NOT EXISTS demo;
SET search_path = demo, public;

DROP TABLE IF EXISTS demo.orders;
CREATE TABLE demo.orders (
  id          bigint         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    varchar(32)    NOT NULL UNIQUE,
  customer    text           NOT NULL,
  amount      numeric(12, 2) NOT NULL,
  status      text           NOT NULL DEFAULT 'pending',
  created_at  timestamptz    NOT NULL DEFAULT now()
);

INSERT INTO demo.orders (order_no, customer, amount, status) VALUES
  ('SO2026001', 'alice', 120.00, 'paid'),
  ('SO2026002', 'bob',    80.50, 'pending'),
  ('SO2026003', 'carol', 300.00, 'paid'),
  ('SO2026004', 'dave',   10.00, 'cancelled');
text
CREATE SCHEMA
SET
DROP TABLE
CREATE TABLE
INSERT 0 4

第二步：写一个 LANGUAGE sql 的表达式封装（含税计算）

sql
CREATE FUNCTION demo.with_tax(p_amount numeric, p_rate numeric DEFAULT 0.06)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT round(p_amount * (1 + p_rate), 2);
$$;

SELECT demo.with_tax(100.00) AS default_rate,
       demo.with_tax(100.00, 0.13) AS custom_rate,
       demo.with_tax(p_rate => 0.13, p_amount => 100.00) AS named_args;
text
 default_rate | custom_rate | named_args
--------------+-------------+------------
       106.00 |      113.00 |     113.00
(1 row)

第三步：写一个返回行集的 plpgsql 函数

sql
CREATE FUNCTION demo.order_summary(p_status text)
RETURNS TABLE(order_count bigint, total_amount numeric)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT count(*), COALESCE(sum(o.amount), 0)
  FROM demo.orders AS o
  WHERE o.status = p_status;
END;
$$;

SELECT * FROM demo.order_summary('paid');
text
 order_count | total_amount
-------------+--------------
           2 |       420.00
(1 row)

第四步：验证 sql 函数被内联（计划里看不到函数调用）

sql
EXPLAIN (COSTS OFF)
SELECT * FROM demo.orders WHERE amount > demo.with_tax(50.00);
text
              QUERY PLAN
---------------------------------------
 Seq Scan on orders
   Filter: (amount > 53.00)
(2 rows)

with_tax(50.00) 是 IMMUTABLE 的 sql 函数且参数是常量，优化器在计划期直接常量折叠成 53.00，函数调用整个消失。把同一个函数改成 plpgsql 就会退化成不可折叠的黑盒调用——原因见 SQL 函数 vs PL/pgSQL 函数。

第五步：查看函数元数据与源码

sql
SELECT p.proname,
       pg_get_function_result(p.oid)            AS result,
       l.lanname,
       p.provolatile                            AS vol,
       pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc AS p
JOIN pg_language AS l ON l.oid = p.prolang
WHERE p.pronamespace = 'demo'::regnamespace
ORDER BY p.proname;
text
   proname     |               result               | language | vol |      args
---------------+------------------------------------+----------+-----+-----------------
 order_summary | TABLE(order_count bigint, total_amount numeric) | plpgsql | s | p_status text
 with_tax      | numeric                            | sql      | i   | p_amount numeric, p_rate numeric
(2 rows)

第六步：收紧权限

sql
-- 撤销默认的 PUBLIC 执行权限，只授权给应用角色
REVOKE ALL ON FUNCTION demo.with_tax(numeric, numeric) FROM PUBLIC;

SELECT proname, proacl
FROM pg_proc
WHERE proname = 'with_tax' AND pronamespace = 'demo'::regnamespace;
text
  proname  |     proacl
-----------+----------------
 with_tax  | {ch=X/ch}
(1 row)

第七步：改函数的正确姿势（DROP + CREATE）

sql
-- 想把返回类型从 numeric 改成 text：OR REPLACE 会报错，必须重建
DROP FUNCTION IF EXISTS demo.with_tax(numeric, numeric);

CREATE FUNCTION demo.with_tax_label(p_amount numeric, p_rate numeric DEFAULT 0.06)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT to_char(round(p_amount * (1 + p_rate), 2), 'FM999999990.00') || ' CNY';
$$;

SELECT demo.with_tax_label(100.00) AS label;
text
    label
--------------
 106.00 CNY
(1 row)

第八步：清理

sql
DROP FUNCTION demo.with_tax_label(numeric, numeric);
DROP FUNCTION demo.order_summary(text);
DROP FUNCTION demo_add_one(integer);
DROP FUNCTION demo_grade_of(integer);
DROP FUNCTION demo_first_even(integer);
DROP FUNCTION demo_evens(integer);
DROP FUNCTION demo_fib(integer);
DROP FUNCTION demo_grade_sql(integer);
DROP FUNCTION demo_bad();
DROP FUNCTION demo_good();
DROP FUNCTION demo_outer();
DROP PROCEDURE demo_purge_orders(integer);
DROP SCHEMA demo CASCADE;
text
DROP FUNCTION
...
DROP SCHEMA

注意事项 ​

  • OR REPLACE 不能改返回类型、不能改参数名。改这两样只能 DROP 重建，报错分别是 cannot change return type of existing function 与 cannot change name of input parameter。

  • OR REPLACE 改参数类型 ≠ 替换。参数类型属于签名，改了等于新建函数，旧的仍留在库里，形成难以察觉的重载。

    sql
    -- 错误！以为在「升级」函数，其实造出了第二个签名
CREATE OR REPLACE FUNCTION demo_release(p_items bigint) RETURNS integer
LANGUAGE sql AS $$ SELECT p_items::integer $$;

  • DROP FUNCTION 不带签名在多签名时会报 is not unique，务必写全参数类型，或用 'f(integer)'::regprocedure。

  • 不写易变性就是 VOLATILE。这是「忘了标」导致性能劣化的根源：优化器无法常量折叠、无法下推索引条件。

  • AS $$ ... $$ 只是一段字符串，plpgsql 的函数体到第一次执行才编译。创建成功不代表能用，务必立即调用一次。

  • LANGUAGE sql 的函数体里不能写 BEGIN ... END、不能写 RETURN。前者是 plpgsql 语法，后者的替代品是「最后一条语句即返回值」。

  • 新函数的 EXECUTE 默认开放给 PUBLIC。会写数据的函数、SECURITY DEFINER 函数必须先 REVOKE 再按需 GRANT。

  • SECURITY DEFINER 默认不固定 search_path，是明确的提权风险。未加 SET search_path 前不要在生产使用，细节见 SECURITY DEFINER / INVOKER。

  • 参数名与列名同名会导致歧义。WHERE id = id 在 plpgsql 里会报 column reference is ambiguous，解法见 函数参数与返回类型。

  • 函数名大小写：不加引号时 Add_One 与 add_one 是同一个函数；加双引号能区分大小写，但会带来「到处都要加引号」的维护负担，不推荐。

  • sql 函数体里不能执行事务控制命令。COMMIT / ROLLBACK / SAVEPOINT / VACUUM 都会报错，这类操作必须用过程（CALL）。

  • 不要把简单表达式封装成函数。f(x) = x * 1.06 直接写在查询里，省掉一次函数解析与调用开销。

  • CREATE FUNCTION 需要语言、参数类型、返回类型的 USAGE 权限，只给 CREATE 权限是不够的。

  • 改名只能用 ALTER FUNCTION ... RENAME TO；ALTER FUNCTION 还能改易变性、安全属性、SET 子句、所有者和注释，但同样不能改参数类型与返回类型。

最佳实践 ​

  • 默认选 LANGUAGE sql。能写成单条 SELECT 就不要用 plpgsql，内联带来的性能差距常是数量级。

    sql
    CREATE FUNCTION app_calc_tax(p_amount numeric) RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN round(p_amount * 1.06, 2);      -- PostgreSQL 14+ 的紧凑写法

  • 显式声明易变性。纯计算的标 IMMUTABLE；读表但语句内稳定的标 STABLE；会写库或含 random() / nextval() 的标 VOLATILE。永远不要依赖默认值。

  • 函数体一律用美元引用，并在体内含 $$ 时给外层换标签：

    sql
    AS $body$
  ...
$body$;

  • 函数名加业务前缀，避开内置函数与扩展函数：app_、demo_、shop_。

  • 参数统一加 p_ 前缀，从根上避免与列名撞车。

  • 把函数定义纳入版本管理。用 pg_get_functiondef() 导出真身，脚本以 DROP FUNCTION IF EXISTS ...; 开头保证可重复执行。

  • 新函数创建后立即调用一次自检，尤其 plpgsql 函数：

    sql
    SELECT app_calc_tax(100.00);   -- 语法错误在这一刻暴露

  • 改函数遵守「先 DROP 再 CREATE」，让依赖对象报错暴露；只有在「函数体微调、签名与返回类型完全不变」时才用 OR REPLACE。

  • 权限从源头收紧，一次性解决所有新建函数的默认开放问题：

    sql
    ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

  • 生产环境一律写 COMMENT ON FUNCTION，说明参数含义、副作用与幂等性：

    sql
    COMMENT ON FUNCTION app_calc_tax(numeric)
  IS '按 6% 税率计算含税金额；纯函数，可安全用于生成列';

  • 只在需要跨事务边界时才用过程，其余场景用函数，保持可组合性。

  • 需要「查询 + 副作用」时优先 INSERT ... RETURNING 形式的 sql 函数，一次往返完成写入与回传。

下一节

继续学习：函数参数与返回类型

参考链接 ​