首页

函数编写

— 数据库原理及应用实验 —

Haifeng Xu

(hfxu@yzu.edu.cn)

参考文献: Regina Obe Leo Hsu 著, 丁奇鹏译《PostgreSQL即学即用》

函数

将若干 SQL 语句组合在一起然后作为一个单元来处理, 这种机制在一些数据库中被称为存储过程, 例如 MySQL. 有的称为自定义函数. 而 PostgreSQL 统一称为函数.

函数不是仅仅将一堆 SQL 语句编排在一起即可, 其中还需要使用过程化语言(Procedural Language, PL)来对 SQL 语句的执行过程进行控制.

在 PostgreSQL 中, 你可以选择使用不同的语言来编写函数, 可选择的语言有很多, 其中 SQL、C、PL/pgSQL、PL/Perl、PL/Python 一般都会随 PostgreSQL 安装包附带.

从 PostgreSQL 9.2 版开始新增了对 PL/V8 语言的支持, 通过它你可以使用 JavaScript 语言来编写函数.

执行 SELECT lanname FROM pg_language; 可以查到已安装的语言列表.

postgres@localhost:5436 tutorial# select lanname from pg_language;
 lanname
----------
 internal
 c
 sql
 plpgsql
(4 行记录)

函数功能基础知识介绍

函数的基本结构如下:

CREATE OR REPLACE FUNCTION func_name(arg1 arg1_datatype DEFAULT arg1_default)
RETURNS some_type | set_of_some_types | TABLE(...) AS
$$
body_of_function
$$
LANGUAGE language_of_function

定义函数时可以添加一些标记符来优化执行效率或提升安全性, 支持的标记符列表如下.

LANGUAGE (使用的编程语言): 指明本函数使用的编程语言, 当然该语言必须在当前函数所在的数据库中已安装.
VOLATILITY (结果的稳定性): 该标记符可以告诉查询规划器当该函数执行完毕后得到的结果是否可以缓存下来以供下次使用. 它有以下几个可选值.
1. immutable (结果恒定不变)
2. stable (结果相对稳定)
3. volatile (结果不稳定)
4. strict (严格模式)
COST (执行成本估计)
ROWS (返回结果集的行数估计)
SECURITY DEFINER (安全控制符)

触发器和触发器函数

借助触发器机制可以实现自动捕捉数据变化事件并进行相应处理. PostgreSQL 对表和视图均支持建触发器.

聚合操作

大多数其他数据库仅允许使用 ANSI SQL 标准中定义的那些聚合函数, 比如 MIN、MAX、AVG、SUM、COUNT 等. 在 PostgreSQL 中则无此限制, 你可以自行实现比以上函数功能更复杂的聚合函数.

在 PostgreSQL 中, 一个聚合函数同时也可以作为窗口函数来使用.

你可以使用 PostgreSQL 所支持的包括 SQL 语言在内的几乎任何语言来编写聚合函数.

一个聚合函数一般是基于一个或者多个子函数实现的.

不管使用何种编程语言, 最终将

CREATE AGGREGATE my_agg(input data_type)(
SFUNC = state function name,
STYPE = state_type,
FINALFUNC = final_function_name,
INITCOND = initial_state_value,
SORTOP = sort_operator
);

SFUNC 即状态切换函数, 它是实现聚合运算的逻辑主体, 它会将自身上一次被调用后生成的计算结果作为本次计算的输入, 同时输入的还有当前新一条的待处理记录, 这样将所有记录一条条累积处理完毕后, 就得到了基于整个目标记录集的“状态”, 也就是最终的聚合结果.

有的情况下, SFUNC 处理得到的结果就是聚合函数需要的最终结果, 但另外一些情况下 SFUNC 处理完毕的结果还需要再进行最终加工才是我们想要的聚合结果, FINALFUNC 就是负责这个最终加工步骤的函数.

FINALFUNC 是可选的, 由于它的作用是对 SFUNC 函数的输出结果做最后的加工, 因此该函数的输入一定是 SFUNC 函数的输出.

INITCOND 也是可选的, 如果设定了该条目, 那么其值会被作为 SFUNC 函数的“状态”的初始值.

最后的 SORTOP 也是可选的, 其值是类似于 > 或 < 这样的运算符, 它的作用是为类似 MAX、MIN 这样的排序操作指定排序运算符. 指定了 SORTOP 运算符后, 规划器会使用索引来进行 MAX、MIN 这样的聚合运算. 由于索引是有序的, 所以可以快速定位到索引的头部或者尾部寻找 MAX、MIN 值, 这样就不需要对所有记录逐条进行大小值判断, 整体运算速度就得以极大提升.

SORTOP 运算符的使用有一个先决条件, 那就是在聚合运算的目标表上, 以下两条语句的执行结果必须完全相同.

SELECT agg(col) FROM sometable;
SELECT col FROM sometable ORDER BY col USING sortop LIMIT 1;

使用 SQL 语言来编写函数

SQL 不是一种过程式语言, 因此你就无法用上比如条件分支判断这种过程控制语句. 此外还有一个更严重的限制, 那就是无法进行根据传入到函数中的实参即时组合成动态的SQL语句.

SQL 函数也有其优点. 查询规划器可以深入到 SQL 函数内部并对其中每一条 SQL 语句进行分析和优化, 该过程被称为 inlining, 即内联处理. 规划器对于别的语言编写的函数只能当成黑盒处理. 内联处理机制使得 SQL 函数能够充分利用索引从而提高执行效率.

编写基本的 SQL 函数

示例. 创建一个SQL函数, 其返回值为新插入的记录的唯一 ID.

CREATE OR REPLACE FUNCTION write_to_log(param_user_name varchar, param_description text)
RETURNS integer AS
$$
INSERT INTO logs(user_name, description) VALUES($1, $2)
RETURNING log_id;
$$
LANGUAGE 'sql' VOLATILE;

在创建此函数前, 检查以下 logs 表.

postgres@localhost:5436 tutorial# select * from logs;
 log_id | user_name | description |         log_ts
--------+-----------+-------------+------------------------
      4 | hfxu      | logged in   | 2025-11-28 12:59:00+08
      1 | robe      | logged in   | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out  | 2025-11-21 23:20:00+08
      3 | mike      | logged in   | 2025-11-22 22:25:00+08
(4 行记录)

postgres@localhost:5436 tutorial# CREATE OR REPLACE FUNCTION write_to_log(param_user_name varchar, param_description text)
tutorial-# RETURNS integer AS
tutorial-# $$
tutorial$# INSERT INTO logs(user_name, description) VALUES($1, $2)
tutorial$# RETURNING log_id;
tutorial$# $$
tutorial-# LANGUAGE 'sql' VOLATILE;
CREATE FUNCTION
时间：28.797 ms

使用 \df 列出已定义的函数. 如果后面跟函数名, 则列出此函数的具体信息.

postgres@localhost:5436 tutorial# \df write_to_log
                                                 函数列表
 架构模式 |     名称     | 结果资料型别 |                       参数资料型别                        | 型别
----------+--------------+--------------+-----------------------------------------------------------+------
 public   | write_to_log | integer      | param_user_name character varying, param_description text | 常规
(1 行记录)

函数的所有信息都存储在 pg_proc 表中.

postgres@localhost:5436 tutorial# select proname, prosrc from pg_proc where proname='write_to_log';
   proname    |                         prosrc
--------------+---------------------------------------------------------
 write_to_log |                                                        +
              | INSERT INTO logs(user_name, description) VALUES($1, $2)+
              | RETURNING log_id;                                      +
              |
(1 行记录)

测试,

SELECT write_to_log('alejandro', 'Woke up at noon.') As new_id;

postgres@localhost:5436 tutorial# SELECT write_to_log('alejandro', 'Woke up at noon.') As new_id;
 new_id
--------
      5
(1 行记录)


时间：16.856 ms
postgres@localhost:5436 tutorial# select * from logs;
 log_id | user_name |   description    |           log_ts
--------+-----------+------------------+----------------------------
      4 | hfxu      | logged in        | 2025-11-28 12:59:00+08
      5 | alejandro | Woke up at noon. | 2025-12-04 19:33:47.583+08
      1 | robe      | logged in        | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out       | 2025-11-21 23:20:00+08
      3 | mike      | logged in        | 2025-11-22 22:25:00+08
(5 行记录)

类似地, 也可以在 SQL 函数中更新数据并返回一个标量或者不返回. 示例如下.

CREATE OR REPLACE FUNCTION
update_logs(log_id int, param_user_name varchar, param_description text)
RETURNS void AS
$$
UPDATE logs SET user_name=$2, description=$3, 
  log_ts=CURRENT_TIMESTAMP WHERE log_id=$1;
$$
LANGUAGE 'sql' VOLATILE;

postgres@localhost:5436 tutorial# CREATE OR REPLACE FUNCTION
tutorial-# update_logs(log_id int, param_user_name varchar, param_description text)
tutorial-# RETURNS void AS
tutorial-# $$
tutorial$# UPDATE logs SET user_name=$2, description=$3,
tutorial$#   log_ts=CURRENT_TIMESTAMP WHERE log_id=$1;
tutorial$# $$
tutorial-# LANGUAGE 'sql' VOLATILE;
CREATE FUNCTION
时间：15.949 ms

使用以下语句来调用此函数.

SELECT update_logs(5, 'alejandro', 'Fell back asleep.');

postgres@localhost:5436 tutorial# SELECT update_logs(5, 'alejandro', 'Fell back asleep.');
 update_logs
-------------
 NULL
(1 行记录)


时间：11.790 ms
postgres@localhost:5436 tutorial# select * from logs;
 log_id | user_name |    description    |           log_ts
--------+-----------+-------------------+----------------------------
      4 | hfxu      | logged in         | 2025-11-28 12:59:00+08
      5 | alejandro | Fell back asleep. | 2025-12-04 19:39:07.292+08
      1 | robe      | logged in         | 2025-11-20 23:15:00+08
      2 | lhsu      | logged out        | 2025-11-21 23:20:00+08
      3 | mike      | logged in         | 2025-11-22 22:25:00+08
(5 行记录)

返回结果集的三种办法

基本上所有编程语言编写的函数都支持返回结果集, SQL 函数也不例外. SQL 函数有三种返回结果集的办法:

第一种是 ANSI SQL 标准中规定的 RETURNS TABLE 语法.
第二种是使用 OUT 形参.
第三种是使用复合数据类型.

第一种的 RETURNS TABLE 语法是从 PostgreSQL 8.3 版才开始支持的. 其他数据库一般也都是基于该语法来实现结果集的返回.

下面的示例展现了三种返回结果集的方法.

使用 RETURNS TABLE 语法

CREATE OR REPLACE FUNCTION select_logs_rt(param_user_name varchar)
RETURNS TABLE (log_id int, user_name varchar(50), description text, log_ts timestamptz) AS
$$
SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

使用 OUT 形参

CREATE OR REPLACE FUNCTION select_logs_out(param_user_name varchar, OUT log_id int,
  OUT user_name varchar, OUT description text, OUT log_ts timestamptz)
RETURNS SETOF record AS
$$
SELECT * FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

使用复合数据类型

CREATE OR REPLACE FUNCTION select_logs_so(param_user_name varchar)
RETURNS SETOF logs AS
$$
SELECT * FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

以上三种方式实现的函数的调用方法都是一致的.

SELECT * FROM select_logs_xxx('alejandro');

使用SQL语言编写聚合函数

$n$ 个正数 $a_1, a_2, \ldots, a_n$ 的几何平均值定义为 $\sqrt[n]{a_1 a_2\cdots a_n}$. 在金融、经济以及统计学领域, 当样本数字的值域范围变化很大时, 可以使用几何平均值来替代更常见的算术平均数.

几何平均值可以使用更高效的公式来计算: $e^{\frac{1}{n}\sum_{i=1}^{n}\ln(x_i)}$

该公式使用了对数来将连乘运算转换为加法运算, 因此计算机执行的效率更高.

为了实现几何平均值运算, 我们使用了两个子函数: 一个状态转换函数, 用于把对数运算结果相加; 一个最终处理函数, 用于对对数之和进行取幂运算. 此外我们还需要指定状态初始值为 0.

CREATE OR REPLACE FUNCTION geom_mean_state(prev numeric[2], next numeric)
RETURNS numeric[2] AS
$$
SELECT
  CASE
    WHEN $2 IS NULL OR $2 = 0 THEN $1
	ELSE ARRAY[COALESCE($1[1],0)+ln($2), $1[2]+1]
  END;
$$
LANGUAGE sql IMMUTABLE;

此状态切换函数有两个输入项:

第一个是前次调用本状态切换函数计算后得到的结果, 其类型为含两个元素的数字型数组;
第二个是本轮计算要处理的样本值.

如果第二个实参的值为 NULL 或 0, 则本轮无需计算, 直接返回实参1的值. 即使用了语句 CASE WHEN \$2 IS NULL OR \$2 = 0 THEN \$1; 否则将本次处理的样本数字的 ln 对数值累加到实参数组的第一个元素上, 并对实参数组的第二个元素值加1. 这样最终得到的结果就是含所有样本数字的 ln 对数值的总和以及总运算次数.

此外我们还需要一个最终处理函数, 该函数中需要将状态转换函数计算得到的两个值相除.

CREATE OR REPLACE FUNCTION geom_mena_final(numeric[2])
RETURNS numeric AS
$$
SELECT CASE WHEN $1[2]>0 THEN exp($1[1]/$1[2]) ELSE 0 END;
$$
LANGUAGE sql IMMUTABLE;

最后, 我们需要将前面定义的这些子函数整合到一起组成一个完整的聚合函数, 语法如下.

CREATE AGGREGATE geom_mean(numeric)(
SFUNC = geom_mean_state,
STYPE = numeric[],
FINALFUNC = geom_mean_final,
INITCOND = '{0,0}'
);

注意这里需要一个初始值 $(0,0)$. 该初始值的类型与 SFUNC 的实参类型一定是一致的.

使用 PL/pgSQL 语言编写函数

下面使用 PL/pgSQL 重写之前的 select_log_rt 函数.

CREATE FUNCTION select_logs_rt(param_user_name varchar)
RETURNS TABLE(log_id int, user_name varchar(50), description text, log_ts timestamptz) AS
$$
BEGIN RETURN QUERY
    SELECT log_id, user_name, description, log_ts FROM logs
	WHERE user_name = param_user_name;
END;
$$
LANGUAGE 'plpgsql' STABLE;

使用 PL/pgSQL 编写触发器函数

由于 PostgreSQL 不支持使用 SQL 编写触发器函数, 因此 PL/pgSQL 就成了编写触发器函数的首选.

总共需要两个步骤:

第一步是写一个触发器函数
第二步是将此触发器函数显式附加到合适的触发器上.

这里第二步将触发器的函数与触发器本身分离开, 这是 PostgreSQL 的一个强大功能. 你可以将同一个触发器函数附加到多个触发器上, 从而实现触发器函数逻辑的重用. 该模式是 PostgreSQL 的独创功能.

由于触发器函数之间是完全独立的, 因此你可以为每个触发器函数选择不同的编程语言, 这些不同语言编写的触发器完全可以协同工作.

PostgreSQL 支持通过一个触发事件(INSERT、UPDATE、DELETE)激活多个触发器, 而且每个触发器可以基于不同语言编写.

示例: 通过触发器对新插入的记录或者修改的记录打时间戳.

定义触发器函数 trig_time_stamper(). 该函数适用于任何带有 upd_ts 字段的表, 该函数会先将upd_ts 字段的值更新为当前时间戳, 然后再返回修改后的记录(RETURN NEW).

CREATE OR REPLACE FUNCTION trig_time_stamper()
RETURNS trigger AS
$$
BEGIN
    NEW.upd_ts := CURRENT_TIMESTAMP;
	RETURN NEW;
END;
$$
LANGUAGE plpgsql VOLATILE;

注意这种修改记录新值的动作应该放在 BEFORE 触发器中. AFTER 触发器被触发时记录新值已经写入表中, 因此时机已经错过, 此时所有对记录新值的修改动作都会被忽略.

CREATE TRIGGER trig_1
BEFORE INSERT ON UPDATE OF session_state, session_id
ON web_sessions
FOR EACH ROW EXECUTE PROCEDURE trig_time_stamper();

这里的触发器是“字段级触发”, 这是9.0版开始支持的一个特性, 通过该特性可以将触发器的触发时机精确到字段级别. 在9.0版之前, 只要发生了 UPDATE 或者 INSERT 动作, 上面示例中的触发器都会被触发; 因此如果要实现字段级触发控制就必须拿 OLD.some_column 和 NEW.some_column 进行对比, 找到发生变化的字段, 然后才能判定是否要进行“字段级触发”.

请注意: INSTEAD OF 触发器不支持字段级触发这个特性.

使用 PL/Python 语言编写函数

从 9.0 版开始, PostgreSQL 支持 Python2 和 Python3 两种语言.

你可以在同一个数据库中同时安装 PL/Python2U 和 PL/Python3U 这两个语言包, 但在同一个用户会话上不同同时使用这两种语言.

这意味着你不能在同一个语句中同时调用分别由 PL/Python2U 和 PL/Python3U 编写的函数. 你在系统中会见到一种叫做 PL/PythonU 的语言, 它实际上是系统为了保持前向兼容而为 PL/Python2U 语言建的一个别名.

首先搭建 Python 运行环境, 然后为 PostgreSQL 安装 Python 语言扩展包.

CREATE EXTENSION plpython2U;
CREATE EXTENSION plpython3U;

End

Thanks very much!

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

函数

函数

函数功能基础知识介绍

函数功能基础知识介绍

触发器和触发器函数

触发器和触发器函数

聚合操作

聚合操作

使用 SQL 语言来编写函数

使用 SQL 语言来编写函数

编写基本的 SQL 函数

返回结果集的三种办法

使用 RETURNS TABLE 语法

使用 OUT 形参

使用复合数据类型

使用SQL语言编写聚合函数

使用 PL/pgSQL 语言编写函数

使用 PL/pgSQL 语言编写函数

使用 PL/pgSQL 编写触发器函数

使用 PL/pgSQL 编写触发器函数

使用 PL/Python 语言编写函数

使用 PL/Python 语言编写函数

End