PostgreSQL SQL函数语法详解-阿里云开发者社区

前面发表了几篇用C语言开发PostgreSQL自定义函数的教程，里面有使用sql语句定义C语言函数的部分都是一笔带过，没有详细解释语法、参数定义等内容，本篇专门讲解一下。
SQL 函数会执行一组任意的 SQL 语句，并返回列表中最后一条查询的结果。在简单（非集合）场景下，仅返回最后一条查询结果的第一行（需注意：除非使用 ORDER BY，否则多行结果的“第一行”是未定义的）。若最后一条查询未返回任何行，则返回空值（null）。

此外，可通过将函数返回类型声明为 SETOF sometype（或等效地声明为 RETURNS TABLE(columns)），使 SQL 函数返回一个集合（即多行数据）。此时，最后一条查询的所有结果行都会被返回，具体细节见下文。

SQL 函数的主体必须是由分号分隔的 SQL 语句列表，最后一条语句后的分号可选。除非函数声明返回 void，否则最后一条语句必须是 SELECT，或带有 RETURNING 子句的 INSERT、UPDATE、DELETE、MERGE。

SQL 语言中的任意命令集合都可封装为函数，除 SELECT 查询外，还可包含数据修改语句（INSERT/UPDATE/DELETE/MERGE）及其他 SQL 命令（注意：事务控制命令如 COMMIT、SAVEPOINT，部分工具命令如 VACUUM 无法在 SQL 函数中使用）。但最终命令必须是 SELECT，或带有 RETURNING 子句且返回值与函数声明的返回类型一致的语句。若需定义仅执行操作但无有效返回值的 SQL 函数，可将其声明为返回 void。例如，以下函数用于删除 emp 表中工资为负数的行：

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

也可将其编写为存储过程，从而规避返回类型的问题：

CREATE PROCEDURE clean_emp() AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

CALL clean_emp();

在这类简单场景中，返回 void 的函数与存储过程的差异主要体现在语法风格上。但存储过程支持函数不具备的额外功能（如事务控制），且存储过程符合 SQL 标准，而返回 void 是 PostgreSQL 的扩展特性。

CREATE FUNCTION 命令要求函数体以字符串常量形式编写，通常使用美元引号最为便捷。若选择常规单引号字符串语法，需将函数体中的单引号（'）和反斜杠（\）转义。

一、SQL 函数的参数

SQL 函数的参数可在函数体中通过名称或数字引用，两种方式的示例如下：

按名称引用

声明函数参数时指定名称，然后在函数体中直接使用该名称。若参数名与函数内当前 SQL 命令中的列名冲突，列名优先级更高；如需覆盖此行为，需用函数名限定参数名（即 function_name.argument_name）。（若此限定名与带限定的列名冲突，仍优先使用列名；可通过为 SQL 命令中的表指定别名避免歧义。）

按数字引用（旧方式）

使用 $n 语法引用参数：$1 表示第一个输入参数，$2 表示第二个，依此类推。无论参数是否声明名称，此方式均有效。

若参数为复合类型，可通过点语法（如 argname.fieldname 或 $1.fieldname）访问参数的属性；同理，可能需要用函数名限定参数名以消除歧义。

注意：SQL 函数参数仅能作为数据值使用，不能作为标识符。例如：
INSERT INTO mytable VALUES ($1);  -- 合法：$1 作为值
INSERT INTO $1 VALUES (42);       -- 非法：$1 作为标识符（表名）
提示：PostgreSQL 9.2 新增了按名称引用 SQL 函数参数的功能，需兼容旧版本服务器的函数必须使用 $n 语法。

二、基类型上的 SQL 函数

最简单的 SQL 函数无参数，仅返回基类型（如整数）：

-- 方式1：美元引号
CREATE FUNCTION one() RETURNS integer AS {mathJaxContainer[0]} LANGUAGE SQL;

-- 方式2：单引号字符串
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

注意：我们在函数体中为结果定义了列别名 result，但该别名在函数外部不可见，因此结果列名显示为 one（函数名）而非 result。

定义接收基类型参数的 SQL 函数也同样简单：

-- 按名称引用参数
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS {mathJaxContainer[1]} LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

-- 按数字引用参数（省略参数名）
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS {mathJaxContainer[2]} LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

以下是更实用的示例（用于扣减银行账户余额）：

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS {mathJaxContainer[3]} LANGUAGE SQL;

-- 调用函数：扣减账户17的余额100.00
SELECT tf1(17, 100.0);

本例中，第一个参数命名为 accountno，与 bank 表的列名冲突；在 UPDATE 命令中，accountno 指向列 bank.accountno，因此必须用 tf1.accountno 引用参数（也可通过修改参数名避免冲突）。

实际场景中，函数返回常量 1 意义不大，更合理的定义是返回更新后的余额：

-- 方式1：先更新，再查询余额
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS {mathJaxContainer[4]} LANGUAGE SQL;

-- 方式2：使用 RETURNING 子句（更简洁）
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS {mathJaxContainer[5]} LANGUAGE SQL;

若 SQL 函数中最后一条 SELECT 或 RETURNING 子句的返回类型与函数声明的结果类型不完全一致，PostgreSQL 会尝试通过隐式转换或赋值转换自动转换类型；若无法自动转换，则需手动编写显式转换。例如，将 add_em 函数改为返回 float8 类型：

CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS {mathJaxContainer[6]} LANGUAGE SQL;

三、复合类型上的 SQL 函数

编写接收复合类型参数的函数时，需指定参数及该参数的目标属性（字段）。例如，假设 emp 表存储员工数据（其表名同时也是每行对应的复合类型名），以下函数 double_salary 用于计算员工工资翻倍后的值：

-- 创建 emp 表
CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

-- 定义函数：接收 emp 复合类型参数，返回 numeric
CREATE FUNCTION double_salary(emp) RETURNS numeric AS {mathJaxContainer[7]} LANGUAGE SQL;

-- 调用函数：传入整行数据
SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

注意：

使用 $1.salary 语法选择参数行值的某个字段；
调用时，table_name.* 用于将表的当前整行作为复合值传入函数；也可仅使用表名（如 double_salary(emp)），但此用法已被废弃。

可通过 ROW 构造器动态构造复合参数值，例如调整传入函数的数据：

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

也可定义返回复合类型的函数，例如返回单个 emp 行：

CREATE FUNCTION new_emp() RETURNS emp AS {mathJaxContainer[8]} LANGUAGE SQL;

本例中为每个属性指定了常量值，实际可替换为任意计算逻辑。

定义此类函数需注意两个关键点：

查询的选择列表顺序必须与复合类型的列顺序完全一致（列名仅作可读性提示，对系统无意义）；
需确保每个表达式的类型可转换为复合类型对应列的类型，否则会报错：
```
ERROR:  return type mismatch in function declared to return emp
DETAIL:  Final statement returns text instead of point at column 4.
```
与基类型场景相同，系统仅自动执行隐式/赋值转换，不会插入显式转换。

也可通过以下方式定义相同函数：

CREATE FUNCTION new_emp() RETURNS emp AS {mathJaxContainer[9]} LANGUAGE SQL;

此方式返回单个符合类型的列，在某些场景下更便捷（如通过调用其他函数生成复合值，或返回基于复合类型的域）。

调用返回复合类型的函数有两种方式：

-- 方式1：作为值表达式调用（返回单行复合值）
SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

-- 方式2：作为表函数调用（展开为列）
SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

若仅需获取复合类型函数结果的某个字段，可使用以下语法：

-- 方式1：括号包裹函数调用（避免解析歧义）
SELECT (new_emp()).name;

 name
------
 None

-- 方式2：函数式字段提取语法
SELECT name(new_emp());

 name
------
 None

也可将复合类型函数的结果传递给另一个接收对应行类型的函数：

CREATE FUNCTION getname(emp) RETURNS text AS {mathJaxContainer[10]} LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

四、带输出参数的 SQL 函数

可通过输出参数（OUT）定义函数结果，示例如下：

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

输出参数的核心价值在于便捷定义返回多列的函数：

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT x + y, x * y'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 row)

其本质是为函数结果创建了一个匿名复合类型，等效于：

-- 先定义复合类型
CREATE TYPE sum_prod AS (sum int, product int);
-- 再定义函数返回该类型
CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

无需单独定义复合类型是输出参数的主要优势，且输出参数的名称会决定匿名复合类型的列名（若省略名称，系统会自动生成）。

注意：
从 SQL 调用带输出参数的函数时，输出参数无需包含在调用参数列表中（PostgreSQL 仅将输入参数纳入函数调用签名）；
删除函数时，仅需指定输入参数：
DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);  -- 等效，推荐

参数可标记为 IN（默认）、OUT、INOUT、VARIADIC：

INOUT：兼具输入（调用参数）和输出（结果记录）属性；
VARIADIC：输入参数，特殊处理方式见下文。

五、带输出参数的 SQL 存储过程

存储过程也支持输出参数，但行为与函数不同：调用存储过程时，输出参数必须包含在参数列表中。例如，前文的银行账户扣减逻辑可改写为存储过程：

CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS {mathJaxContainer[11]} LANGUAGE SQL;

-- 调用：输出参数位置传 NULL
CALL tp1(17, 100.0, NULL);

若输出参数位置传入非 NULL 值，该值需可隐式转换为参数声明类型，但系统不会计算该表达式（仅占位）。

从 PL/pgSQL 调用存储过程时，输出参数位置需传入变量（用于接收结果）。

六、可变参数数量的 SQL 函数

SQL 函数可声明接收可变数量的参数，要求所有“可选参数”类型相同（可选参数会以数组形式传入函数）。需将最后一个参数标记为 VARIADIC，且该参数必须是数组类型：

-- 定义：接收 numeric 数组，返回最小值
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS {mathJaxContainer[12]} LANGUAGE SQL;

-- 调用：传入多个 numeric 值（自动封装为数组）
SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)

其本质是将 VARIADIC 位置及之后的所有实际参数封装为一维数组，等效于：

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);  -- 显式传入数组（必须加 VARIADIC）

注意：
直接传入数组（如 mleast(ARRAY[10, -1, 5, 4.4])）会报错，因为 VARIADIC 参数匹配的是数组元素类型，而非数组本身；

VARIADIC 仅能附加在函数调用的最后一个实际参数上；

传入空数组需显式指定：mleast(VARIADIC ARRAY[]::numeric[])；
可变参数无法通过命名参数调用（除非加 VARIADIC）：
SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);  -- 合法
SELECT mleast(arr => 10);  -- 非法

七、带参数默认值的 SQL 函数

函数可声明部分/全部输入参数的默认值，调用时若实际参数数量不足，系统会自动填充默认值。由于参数仅能从末尾省略，因此默认值参数后的所有参数都必须有默认值（即使支持命名参数，此限制仍为保证位置参数调用的合理性）。

示例：

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS {mathJaxContainer[13]};

SELECT foo(10, 20, 30);  -- 10+20+30 → 60
SELECT foo(10, 20);      -- 10+20+3 → 33
SELECT foo(10);          -- 10+2+3 → 15
SELECT foo();            -- 报错：第一个参数无默认值
ERROR:  function foo() does not exist

可使用 = 替代关键字 DEFAULT。

八、作为表源的 SQL 函数

所有 SQL 函数均可用于查询的 FROM 子句，返回复合类型的函数尤为实用：

若函数返回基类型：生成单列表；
若函数返回复合类型：为复合类型的每个属性生成一列。

示例：

-- 创建测试表
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

-- 定义函数：返回 foo 类型
CREATE FUNCTION getfoo(int) RETURNS foo AS {mathJaxContainer[14]} LANGUAGE SQL;

-- 调用函数作为表源
SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

可见，函数结果的列可像普通表列一样操作。本例仅返回一行，因为未使用 SETOF（见后续内容）。

九、返回集合的 SQL 函数

若 SQL 函数声明为 RETURNS SETOF sometype，最后一条查询会执行完毕，其输出的每一行都会作为结果集的一个元素返回。

此特性通常用于 FROM 子句中，函数返回的每一行都会成为查询可见的表行：

-- 定义返回集合的函数
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS {mathJaxContainer[15]} LANGUAGE SQL;

-- 调用：返回多行
SELECT * FROM getfoo(1) AS t1;
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

也可通过输出参数定义返回多列的集合：

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

-- 返回 SETOF record（多列集合）
CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS {mathJaxContainer[16]} LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

关键：需声明 RETURNS SETOF record 表示返回多行；若仅有一个输出参数，可直接写该参数类型（而非 record）。

LATERAL 关键字

常需多次调用返回集合的函数，且每次调用的参数来自表/子查询的连续行，此时推荐使用 LATERAL 关键字。示例（枚举树形结构的子节点）：

-- 测试数据
CREATE TABLE nodes (name text, parent text);
INSERT INTO nodes VALUES
    ('Top', ''), ('Child1', 'Top'), ('Child2', 'Top'),
    ('Child3', 'Top'), ('SubChild1', 'Child1'), ('SubChild2', 'Child1');

-- 定义返回子节点的函数
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS {mathJaxContainer[17]} LANGUAGE SQL STABLE;

-- 基础调用
SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3

-- 结合 LATERAL 调用
SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2

选择列表中的集合返回函数

返回集合的函数也可用于查询的选择列表：查询生成每行时，函数会被调用，其结果集的每个元素都会生成一行输出：

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2

注意：Child2/Child3 无输出行，因为函数对这些参数返回空集。

PostgreSQL 对选择列表中集合返回函数的处理，几乎等效于将其写入 LATERAL FROM 子句。若选择列表中有多个集合返回函数，它们会“同步执行”：底层查询的每行对应输出行的数量，等于返回行数最多的函数的行数，不足的部分用 NULL 填充。

注意：

集合返回函数不能用于条件求值构造（如 CASE/COALESCE），否则会触发解析错误；

若函数最后一条命令是带 RETURNING 的 INSERT/UPDATE/DELETE/MERGE，即使函数未声明 SETOF 或调用查询未获取所有结果行，该命令仍会执行完毕（多余的 RETURNING 行被静默丢弃，但表修改会生效）；

PostgreSQL 10 之前，选择列表中多个集合返回函数的行为不一致，推荐使用 LATERAL 保证跨版本兼容性。

十、返回 TABLE 的 SQL 函数

可通过 RETURNS TABLE(columns) 声明返回集合的函数，这等效于使用 OUT 参数 + RETURNS SETOF record（或对应类型）。此语法符合 SQL 标准，可移植性更好：

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS {mathJaxContainer[18]} LANGUAGE SQL;

注意：使用 RETURNS TABLE 时，不能显式声明 OUT/INOUT 参数，所有输出列必须包含在 TABLE 列表中。

十一、多态 SQL 函数

SQL 函数可声明接收/返回 多态类型。示例（从两个任意类型元素构建数组）：

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS {mathJaxContainer[19]} LANGUAGE SQL;

-- 调用：需显式指定类型（避免 unknown 类型）
SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {
  1,2}    | {
  a,b}

anyelement 要求参数类型完全一致，若需自动推导通用类型，可使用 anycompatible 系列多态类型：

CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS {mathJaxContainer[20]} LANGUAGE SQL;

-- 自动将 1（int）和 2.5（numeric）提升为 numeric
SELECT make_array2(1, 2.5) AS numericarray;
 numericarray
--------------
 {
  1,2.5}

规则：

允许多态参数搭配固定返回类型，但反之不行（返回 anyelement 需至少一个多态输入参数）；

多态可与输出参数、可变参数结合使用。

示例（多态可变参数）：

-- 返回任意类型数组的最小值
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS {mathJaxContainer[21]} LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);  -- int → -1
SELECT anyleast('abc'::text, 'def');  -- text → abc

十二、带排序规则的 SQL 函数

当 SQL 函数包含一个/多个可排序数据类型的参数时，会根据实际参数的排序规则为每次调用确定一个排序规则。若确定成功（无冲突），所有可排序参数会隐式使用该排序规则，影响函数内对排序敏感的操作：

-- 依赖默认排序规则
SELECT anyleast('abc'::text, 'ABC');
-- 强制使用 C 排序规则
SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

若需函数固定使用某排序规则，可在函数定义中添加 COLLATE 子句：

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS {mathJaxContainer[22]} LANGUAGE SQL;

注意：此方式若应用于非可排序类型会报错；若无法确定通用排序规则，参数使用其数据类型的默认排序规则。

可排序参数的行为可视为一种有限的多态性，仅适用于文本类型。

总结

SQL 函数执行一组 SQL 语句并返回最后一条查询的结果，支持返回单行（基类型/复合类型）或多行（SETOF/RETURNS TABLE），最后一条语句需为 SELECT 或带 RETURNING 的数据修改语句；
函数参数可按名称/数字引用，支持输出参数（便捷返回多列）、可变参数（VARIADIC，封装为数组）、默认值，存储过程的输出参数调用方式与函数不同；
复合类型函数可通过点语法访问字段，返回集合的函数常用于 FROM 子句，结合 LATERAL 可实现参数化多行返回；
多态函数（anyelement/anycompatible）支持通用类型处理，排序规则可隐式继承或显式指定，影响文本类操作的行为。

PostgreSQL SQL函数语法详解

一、SQL 函数的参数

按名称引用

按数字引用（旧方式）

二、基类型上的 SQL 函数

三、复合类型上的 SQL 函数

四、带输出参数的 SQL 函数

五、带输出参数的 SQL 存储过程

六、可变参数数量的 SQL 函数

七、带参数默认值的 SQL 函数

八、作为表源的 SQL 函数

九、返回集合的 SQL 函数

LATERAL 关键字

选择列表中的集合返回函数

十、返回 TABLE 的 SQL 函数

十一、多态 SQL 函数

十二、带排序规则的 SQL 函数

总结

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

PostgreSQL SQL函数语法详解

一、SQL 函数的参数

按名称引用

按数字引用（旧方式）

二、基类型上的 SQL 函数

三、复合类型上的 SQL 函数

四、带输出参数的 SQL 函数

五、带输出参数的 SQL 存储过程

六、可变参数数量的 SQL 函数

七、带参数默认值的 SQL 函数

八、作为表源的 SQL 函数

九、返回集合的 SQL 函数

LATERAL 关键字

选择列表中的集合返回函数

十、返回 TABLE 的 SQL 函数

十一、多态 SQL 函数

十二、带排序规则的 SQL 函数

总结

热门文章

最新文章

相关电子书