PHP小码农

Appearance

PL/Perl 基础 ​

Perl 最出名的能力是正则与文本处理——s///、tr///、split、哈希、列表操作。这些正好是 SQL 里写起来最别扭的东西：清洗半结构化日志、从自由文本里抽字段、批量改写字符串。PL/Perl 让你把这些工作直接放进数据库，函数体就是一段普通 Perl 代码。

和 PL/Python 相比，PL/Perl 有一个本质区别：它默认提供可信（trusted）版本 plperl，普通用户就能创建函数。这让它成为唯一一个「非超级用户也能用」的嵌入式过程语言。本页讲清两个语言名的区别、类型怎么映射、SPI 怎么用、$_TD 怎么写触发器，以及可信模式的真实安全边界。

前置知识

阅读本节前，建议先了解：PL/Python 基础、触发器函数编写

基础概念 ​

PL/Perl 是什么 ​

PL/Perl 是 PostgreSQL 的可加载过程语言扩展，把 Perl 解释器嵌入到数据库后端进程里。函数体是一段普通 Perl 代码，PostgreSQL 的胶水代码把它包进一个 Perl 子程序里执行。

text
应用进程                          数据库后端进程（backend）
────────────                      ──────────────────────────────────
SELECT clean_text('a  {b}');
      │                            ┌─────────────────────────┐
      └──── SQL 请求 ─────────────► │ PostgreSQL 内核          │
                                   │   ↓ 解析、规划、执行      │
                                   │ plperl 语言处理器        │
                                   │   ↓ 把函数体包成          │
                                   │   Perl 子程序             │
                                   │   ↓                     │
                                   │ Perl 解释器              │
                                   │   ├─ 正则 / 文本处理      │
                                   │   ├─ spi_exec_query(SQL) │
                                   │   └─ 哈希 / 数组操作      │
                                   └─────────────────────────┘

官方对它的定位很明确：The main advantage to using PL/Perl is that this allows use, within stored functions and procedures, of the manyfold "string munging" operators and functions available for Perl. Parsing complex strings might be easier using Perl than it is with the string functions and control structures provided in PL/pgSQL.

也就是说：PL/Perl 的主场是字符串处理，不是通用业务逻辑。

关键区别：plperl 与 plperlu 是两个语言名 ​

这是 PL/Perl 与 PL/Python 最大的不同，也是最需要讲清的一点。官方原文：Normally, PL/Perl is installed as a "trusted" programming language named plperl... PL/Perl can also be installed as an "untrusted" language (usually called PL/PerlU). In this case the full Perl language is available. When installing the language, the language name plperlu will select the untrusted PL/Perl variant.

语言名信任级别谁可创建函数能力边界
plperl可信（trusted）任何有 USAGE 权限的用户禁用文件句柄操作、require、use
plperlu不可信（untrusted）仅超级用户完整 Perl，可读写文件、执行系统调用

与 PL/Python 对比一下，差异就很清楚了：

对比项PL/PythonPL/Perl
可信版本❌ 不存在（只有 plpython3u）✅ 有，默认就是 plperl
不可信版本plpython3u（唯一）plperlu
语言名后缀含义u 是常态无后缀 = 可信，u 后缀 = 不可信
普通用户可创建函数❌ 只有超级用户✅ 可信版本可以

可信版本禁用了什么 ​

官方原文：In general, the operations that are restricted are those that interact with the environment. This includes file handle operations, require, and use (for external modules). There is no way to access internals of the database server process or to gain OS-level access with the permissions of the server process, as a C function can do.

操作plperl（可信）plperlu（不可信）
正则、哈希、数组、字符串函数✅✅
open / 读写文件❌ 校验期即报错✅
require / use 外部模块❌✅
system / 反引号执行命令❌✅
spi_exec_query 等 SPI 函数✅✅
elog / quote_* 等工具函数✅✅

被禁用的代码不是运行时才拦，而是在 CREATE FUNCTION 校验阶段就被拒绝。官方示例：

sql
CREATE FUNCTION badfunc() RETURNS integer AS $$
  my $tmpfile = "/tmp/badfile";
  open my $fh, '>', $tmpfile
    or elog(ERROR, qq{could not open the file "$tmpfile": $!});
  print $fh "Testing writing to a file\n";
  close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
  return 1;
$$ LANGUAGE plperl;
text
ERROR:  'open' trapped by operation mask at line 3.
CONTEXT:  compilation of PL/Perl function "badfunc"

同一条函数体，把语言名换成 plperlu，由超级用户创建就能成功执行。

官方对可信模式的安全警告

可信 PL/Perl 的安全性依赖 Perl 的 Opcode 模块。官方原文：Trusted PL/Perl relies on the Perl Opcode module to preserve security. Perl documents that the module is not effective for the trusted PL/Perl use case. If your security needs are incompatible with the uncertainty in that warning, consider executing REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC.

也就是说：如果你对安全要求高，不要把可信 PL/Perl 当作沙箱边界；官方建议直接收回 PUBLIC 的 USAGE 权限。

安装 ​

PL/Perl 由官方核心发行版提供（不是外部项目），但需要单独在数据库里安装：

sql
CREATE EXTENSION plperl;

plperlu 不需要单独安装——同一个扩展同时注册了两个语言名：

sql
-- 查看已安装的过程语言
SELECT lanname, lanpltrusted, lanowner::regrole AS owner
FROM pg_language
WHERE lanname LIKE 'plperl%' OR lanname LIKE 'plpython%'
ORDER BY lanname;
text
 lanname  | lanpltrusted | owner
----------+--------------+----------
 plperl   | t            | postgres
 plperlu  | f            | postgres

lanpltrusted = t 表示可信语言，f 表示不可信。

装到 template1 就不必逐库安装

官方原文：If a language is installed into template1, all subsequently created databases will have the language installed automatically.

sql
\c template1
CREATE EXTENSION plperl;

注意：这只影响之后创建的数据库，已有数据库仍需各自安装。

源码/二进制包注意事项

官方提示：Users of source packages must specially enable the build of PL/Perl during the installation process. Users of binary packages might find PL/Perl in a separate subpackage.

Debian / Ubuntu 上通常需要单独安装 postgresql-plperl-18；RHEL 系为 postgresql18-plperl。编译源码需配置 --with-perl。

与 PL/pgSQL 的定位对比 ​

维度PL/pgSQLPL/Perl
主要优势SQL 原生、性能好、计划缓存文本处理、正则、数据结构
参数传递具名参数，类型化@_ 数组，全是文本
返回值RETURN + 类型化return 文本，或返回引用
执行 SQL直接内嵌 SQL，自动缓存计划spi_exec_query 等 SPI 函数
默认可信✅（plpgsql 是可信语言）✅（plperl）
调试RAISE NOTICEelog(NOTICE, ...)
编译期首次调用时编译首次调用时编译

选择原则：逻辑简单、以 SQL 为主 → PL/pgSQL；逻辑以字符串/文本为主 → PL/Perl。

语法与用法 ​

创建函数 ​

官方语法的形态与普通 SQL 函数完全一致：

sql
CREATE FUNCTION funcname (argument-types)
RETURNS return-type
-- function attributes can go here
AS $$
  # PL/Perl function body goes here
$$ LANGUAGE plperl;

官方对几个语义点写得很直接：

  • The body of the function is ordinary Perl code. In fact, the PL/Perl glue code wraps it inside a Perl subroutine.
  • A PL/Perl function is called in a scalar context, so it can't return a list. 返回列表的请求必须返回引用。
  • In a PL/Perl procedure, any return value from the Perl code is ignored.

匿名代码块 ​

sql
DO $$
  # PL/Perl code
$$ LANGUAGE plperl;

官方说明：An anonymous code block receives no arguments, and whatever value it might return is discarded. Otherwise it behaves just like a function.

最小可运行示例 ​

sql
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
  if ($_[0] > $_[1]) { return $_[0]; }
  return $_[1];
$$ LANGUAGE plperl;

SELECT perl_max(3, 7);
text
 perl_max
----------
        7
(1 row)

常用命令速查 ​

text
-- 语言安装
CREATE EXTENSION plperl;

-- 可信函数（任何有 USAGE 权限的用户）
CREATE FUNCTION f(...) RETURNS ... AS $$ ... $$ LANGUAGE plperl;

-- 不可信函数（仅超级用户）
CREATE FUNCTION f(...) RETURNS ... AS $$ ... $$ LANGUAGE plperlu;

-- 匿名块
DO $$ ... $$ LANGUAGE plperl;

-- 开启严格模式（仅影响之后的编译）
SET plperl.use_strict = on;

-- 收回普通用户的使用权
REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC;

详细说明 ​

参数与返回值：一切都是文本 ​

这是 PL/Perl 最容易踩坑的地方。官方原文：Anything in a function argument that is not a reference is a string, which is in the standard PostgreSQL external text representation for the relevant data type. 以及 The argument values supplied to a PL/Perl function's code are simply the input arguments converted to text form (just as if they had been displayed by a SELECT statement).

text
SQL 值                    →  Perl 里看到的东西
──────────────────────────────────────────────
integer 42                →  "42"（字符串，但用作数字会自动转换）
text 'hello'              →  "hello"
bool true                 →  "t"        ← 注意！不是 1
bool false                →  "f"        ← 注意！Perl 里 "f" 为真！
NULL                      →  undef
bytea '\xdeadbeef'        →  转义后的文本，需 decode_bytea
numeric 1.50              →  "1.50"
数组 {1,2,3}              →  PostgreSQL::InServer::ARRAY 引用
复合类型行                →  哈希引用

反面示例，NULL 会被当成 0：

sql
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
  if ($_[0] > $_[1]) { return $_[0]; }
  return $_[1];
$$ LANGUAGE plperl;

-- 传入 NULL 时行为错误：perl_max(NULL, 5) 会返回 NULL 的「类似 0」比较结果

修正办法有两条：加 STRICT，或在函数体里显式判 defined。官方给了一个更完整的写法：

sql
CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
  my ($x, $y) = @_;
  if (not defined $x) {
    return undef if not defined $y;
    return $y;
  }
  return $x if not defined $y;
  return $x if $x > $y;
  return $y;
$$ LANGUAGE plperl;

返回 SQL NULL 的方式：官方原文 to return an SQL null value from a PL/Perl function, return an undefined value. This can be done whether the function is strict or not.

bool 的陷阱与 TRANSFORM ​

bool 以 't' / 'f' 文本传入，而 Perl 认为 'f' 为真——这是一个静默的逻辑错误源。官方原文：This is problematic, since Perl will not treat 'f' as false!

解决办法是使用 transform，由 bool_plperl 扩展提供：

sql
CREATE EXTENSION bool_plperl;   -- 不可信语言用 bool_plperlu

然后给函数加 TRANSFORM FOR TYPE bool 属性：

sql
CREATE FUNCTION perl_and(bool, bool) RETURNS bool
TRANSFORM FOR TYPE bool
AS $$
  my ($a, $b) = @_;
  return $a && $b;
$$ LANGUAGE plperl;

应用 transform 后，官方原文：bool arguments will be seen by Perl as being 1 or empty, thus properly true or false.

数组与复合类型 ​

返回数组用 Perl 数组引用：

sql
CREATE OR REPLACE FUNCTION returns_array()
RETURNS text[][] AS $$
  return [['a"b','c,d'],['e\\f','g']];
$$ LANGUAGE plperl;

SELECT returns_array();

接收数组时，官方原文：Perl passes PostgreSQL arrays as a blessed PostgreSQL::InServer::ARRAY object. This object may be treated as an array reference or a string.

sql
CREATE OR REPLACE FUNCTION concat_array_elements(text[]) RETURNS TEXT AS $$
  my $arg = shift;
  my $result = "";
  return undef if (!defined $arg);

  # as an array reference
  for (@$arg) {
    $result .= $_;
  }

  # also works as a string
  $result .= $arg;

  return $result;
$$ LANGUAGE plperl;

复合类型按哈希引用传递，键是属性名：

sql
CREATE TABLE employee (
  name text,
  basesalary integer,
  bonus integer
);

CREATE FUNCTION empcomp(employee) RETURNS integer AS $$
  my ($emp) = @_;
  return $emp->{basesalary} + $emp->{bonus};
$$ LANGUAGE plperl;

返回复合类型同理，返回哈希引用即可；官方说明：Any columns in the declared result data type that are not present in the hash will be returned as null values.

sql
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);

CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
  return {f2 => 'hello', f1 => 1, f3 => 'world'};
$$ LANGUAGE plperl;

集合返回：return_next 优先 ​

目标行数多时，用 return_next 逐行返回，避免整个结果集堆在内存里。官方原文：Usually you'll want to return rows one at a time, both to speed up startup time and to keep from queuing up the entire result set in memory. 并且强调 after the last return_next, you must put either return or (better) return undef.

sql
CREATE OR REPLACE FUNCTION perl_set_int(int)
RETURNS SETOF INTEGER AS $$
  foreach (0..$_[0]) {
    return_next($_);
  }
  return undef;
$$ LANGUAGE plperl;

小结果集可以直接返回数组引用：

sql
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
  return [0..$_[0]];
$$ LANGUAGE plperl;

SPI：spi_exec_query 执行 SQL ​

text
$rv = spi_exec_query('SELECT * FROM my_table', 5);

官方说明：spi_exec_query executes an SQL command and returns the entire row set as a reference to an array of hash references. 第二参数 limit 限制返回行数（省略或为 0 表示不限）。

结果对象的访问方式：

表达式含义
$rv->{rows}[$i]->{col}第 i 行的 col 列
$rv->{processed}受影响/返回的行数
$rv->{status}命令状态，如 SPI_OK_INSERT
text
$foo  = $rv->{rows}[$i]->{my_column};
$nrows = $rv->{processed};
$res   = $rv->{status};

只在结果集较小时用 spi_exec_query

官方原文：You should only use this command when you know that the result set will be relatively small. 它会把整个结果集一次性读进内存。

SPI：游标式 spi_query / spi_fetchrow ​

大结果集或希望边取边处理时使用：

text
$sth = spi_query("SELECT ...");
while (defined ($row = spi_fetchrow($sth))) {
    ...
}
spi_cursor_close($sth);   # 未读完全部行时必须显式关闭

官方规则：

  • Normally, spi_fetchrow should be repeated until it returns undef.
  • The cursor returned by spi_query is automatically freed when spi_fetchrow returns undef.
  • If you do not wish to read all the rows, instead call spi_cursor_close to free the cursor. Failure to do so will result in memory leaks.

SPI：预备语句 ​

text
$plan = spi_prepare('SELECT * FROM test WHERE id > $1 AND name = $2', 'INTEGER', 'TEXT');
$rv   = spi_exec_prepared($plan, {limit => 5}, $args);
$sth  = spi_query_prepared($plan, $args);
spi_freeplan($plan);

一套函数对应关系：

函数作用
spi_prepare(command, types...)编译带 $1,$2 占位符的语句，返回计划
spi_exec_prepared(plan [, attrs], args...)执行计划，返回与 spi_exec_query 相同结构
spi_query_prepared(plan, args...)执行计划并返回游标，配合 spi_fetchrow
spi_freeplan(plan)释放计划

spi_exec_prepared 的可选属性哈希目前只支持 limit。官方还提醒：the parameter subscript in spi_prepare is defined via \$1, \$2, \$3, etc., so avoid declaring query strings in double quotes that might easily lead to hard-to-catch bugs. —— 用双引号时 Perl 会试图插值 $1，务必用单引号。

提交与回滚：spi_commit() / spi_rollback() ​

官方原文：Commit or roll back the current transaction. This can only be called in a procedure or anonymous code block (DO command) called from the top level. (Note that it is not possible to run the SQL commands COMMIT or ROLLBACK via spi_exec_query or similar. It has to be done using these functions.) After a transaction is ended, a new transaction is automatically started, so there is no separate function for that.

sql
CREATE PROCEDURE transaction_test1()
LANGUAGE plperl
AS $$
foreach my $i (0..9) {
    spi_exec_query("INSERT INTO test1 (a) VALUES ($i)");
    if ($i % 2 == 0) {
        spi_commit();
    } else {
        spi_rollback();
    }
}
$$;

CALL transaction_test1();

注意：这两个函数不能在普通函数里调用，只能在过程（CREATE PROCEDURE）或顶层 DO 块里。

工具函数 ​

日志：elog(level, msg)，可选级别为 DEBUG、LOG、INFO、NOTICE、WARNING、ERROR。官方原文：ERROR raises an error condition; if this is not trapped by the surrounding Perl code, the error propagates out to the calling query, causing the current transaction or subtransaction to be aborted. This is effectively the same as the Perl die command.

字符串引用：

函数作用
quote_literal($str)转义为 SQL 字符串字面量；undef 输入返回 undef
quote_nullable($str)同上，但 undef 返回字符串 NULL
quote_ident($str)必要时加双引号，转义为合法标识符

二进制：

函数作用
decode_bytea($str)把 bytea 文本转成未转义的二进制
encode_bytea($str)把二进制转成 bytea 转义文本

数组与类型转换：

函数作用
encode_array_literal($arr [, $delim])转成数组字面量字符串
encode_typed_literal($val, $typename)转成指定类型的字符串表示，正确处理嵌套数组与复合类型
encode_array_constructor($arr)转成数组构造器格式，元素用 quote_nullable 引用

判断辅助：

函数作用
looks_like_number($str)按 Perl 规则判断是否像数字；Inf / Infinity 视为数字
is_array_ref($arg)参数是否为数组引用（ARRAY 或 PostgreSQL::InServer::ARRAY）

全局值：%_SHARED ​

官方原文：You can use the global hash %_SHARED to store data, including code references, between function calls for the lifetime of the current session.

sql
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$
  if ($_SHARED{$_[0]} = $_[1]) {
    return 'ok';
  } else {
    return "cannot set shared variable $_[0] to $_[1]";
  }
$$ LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$
  return $_SHARED{$_[0]};
$$ LANGUAGE plperl;

SELECT set_var('sample', 'Hello, PL/Perl!');
SELECT get_var('sample');

%_SHARED 也能存代码引用：

sql
CREATE OR REPLACE FUNCTION myfuncs() RETURNS void AS $$
  $_SHARED{myquote} = sub {
    my $arg = shift;
    $arg =~ s/(['\\])/\\$1/g;
    return "'$arg'";
  };
$$ LANGUAGE plperl;

%_SHARED 按 SQL 角色隔离

官方原文：For security reasons, PL/Perl executes functions called by any one SQL role in a separate Perl interpreter for that role... Thus, two PL/Perl functions will share the same value of %_SHARED if and only if they are executed by the same SQL role.

如果一个会话里用多个角色（SET ROLE、SECURITY DEFINER）执行 PL/Perl 函数，它们不会共享 %_SHARED。官方给出的解法是：make sure that functions that should communicate are owned by the same user, and mark them SECURITY DEFINER.

触发器：$_TD 哈希 ​

官方原文：In a trigger function, the hash reference $_TD contains information about the current trigger event. $_TD is a global variable, which gets a separate local value for each invocation of the trigger.

字段含义
$_TD->{new}{foo}列 foo 的 NEW 值
$_TD->{old}{foo}列 foo 的 OLD 值
$_TD->{name}触发器名
$_TD->{event}INSERT / UPDATE / DELETE / TRUNCATE / UNKNOWN
$_TD->{when}BEFORE / AFTER / INSTEAD OF / UNKNOWN
$_TD->{level}ROW / STATEMENT / UNKNOWN
$_TD->{relid}触发表的 OID
$_TD->{table_name}触发表的名称
$_TD->{relname}同 table_name，已弃用，请改用 table_name
$_TD->{table_schema}触发表的 schema 名
$_TD->{argc}触发器函数参数个数
@{$_TD->{args}}触发器函数参数；argc 为 0 时不存在

行级触发器可返回三种值：

返回值效果
return;执行操作
"SKIP"不执行操作
"MODIFY"表示 NEW 行已被触发器修改

官方完整示例：

sql
CREATE TABLE test (
  i int,
  v varchar
);

CREATE OR REPLACE FUNCTION valid_id() RETURNS trigger AS $$
  if (($_TD->{new}{i} >= 100) || ($_TD->{new}{i} <= 0)) {
      return "SKIP";       # skip INSERT/UPDATE command
  } elsif ($_TD->{new}{v} ne "immortal") {
      $_TD->{new}{v} .= "(modified by trigger)";
      return "MODIFY";     # modify row and execute INSERT/UPDATE command
  } else {
      return;              # execute INSERT/UPDATE command
  }
$$ LANGUAGE plperl;

CREATE TRIGGER test_valid_id_trig
  BEFORE INSERT OR UPDATE ON test
  FOR EACH ROW EXECUTE FUNCTION valid_id();

事件触发器：$_TD->{event} 与 $_TD->{tag} ​

PL/Perl 也能写事件触发器。官方原文：In an event trigger function, the hash reference $_TD contains information about the current trigger event.

字段含义
$_TD->{event}触发的事件名
$_TD->{tag}触发时的命令标签

官方特别注明：The return value of the trigger function is ignored. 与普通触发器不同，这里没有 SKIP / MODIFY 的概念。

sql
CREATE OR REPLACE FUNCTION perlsnitch() RETURNS event_trigger AS $$
  elog(NOTICE, "perlsnitch: " . $_TD->{event} . " " . $_TD->{tag} . " ");
$$ LANGUAGE plperl;

CREATE EVENT TRIGGER perl_a_snitch
  ON ddl_command_start
  EXECUTE FUNCTION perlsnitch();

配置参数 ​

参数作用域说明
plperl.on_initpostgresql.conf / 命令行解释器首次初始化时执行的 Perl 代码。SPI 不可用。可把较长代码放进模块再 require
plperl.on_plperl_init超级用户会话内plperl 解释器被「特化」时执行。在「锁定」之后执行，只能做可信操作
plperl.on_plperlu_init超级用户会话内plperlu 解释器特化时执行，不受可信限制
plperl.use_strict会话 / postgresql.conf为 true 时，之后编译的 PL/Perl 函数启用 strict pragma

关于 on_init，官方给了一个示例与一条安全提醒：

text
plperl.on_init = 'require "plperlinit.pl"'
plperl.on_init = 'use lib "/my/app"; use MyApp::PgInit;'

Any modules loaded by plperl.on_init, either directly or indirectly, will be available for use by plperl. This may create a security risk.

另外，plperl.use_strict 的作用范围要注意：This parameter does not affect functions already compiled in the current session.

静态严格模式 ​

除了全局的 plperl.use_strict，也可以针对单个函数：

sql
CREATE FUNCTION f(...) RETURNS ... AS $$
  use strict;
  ...
$$ LANGUAGE plperl;

官方说明：Perl 5.10.0 以上还可以 use feature。

局限与缺失特性 ​

官方列出的当前缺失项：

  • PL/Perl functions cannot call each other directly.
  • SPI is not yet fully implemented.
  • 用 spi_exec_query 取超大结果集时会全部进内存，应改用 spi_query / spi_fetchrow。
  • 集合返回函数用 return 返回大结果集也有同样问题，应改用 return_next。
  • When a session ends normally, not due to a fatal error, any END blocks that have been defined are executed. Currently no other actions are performed. Specifically, file handles are not automatically flushed and objects are not automatically destroyed.

解释器与隔离模型 ​

两个官方注解值得记住：

PL/PerlU 共用一个解释器

官方原文：While PL/Perl functions run in a separate Perl interpreter for each SQL role, all PL/PerlU functions executed in a given session run in a single Perl interpreter (which is not any of the ones used for PL/Perl functions). This allows PL/PerlU functions to share data freely, but no communication can occur between PL/Perl and PL/PerlU functions.

Perl 需要 usemultiplicity 或 useithreads 才能多解释器

官方原文：Perl cannot support multiple interpreters within one process unless it was built with the appropriate flags, namely either usemultiplicity or useithreads. (usemultiplicity is preferred unless you actually need to use threads.) ... If PL/Perl is used with a copy of Perl that was not built this way, then it is only possible to have one Perl interpreter per session, and so any one session can only execute either PL/PerlU functions, or PL/Perl functions that are all called by the same SQL role.

实战示例 ​

示例一：正则清洗脏文本 ​

PL/Perl 的看家本领。下面把一段自由文本规范化为「单词 + 单个空格」，并去掉首尾空白：

sql
CREATE OR REPLACE FUNCTION clean_text(input text) RETURNS text AS $$
  my $s = shift;
  return undef if !defined $s;
  $s =~ s/\s+/ /g;          # 连续空白折叠为一个空格
  $s =~ s/^ //;              # 去头
  $s =~ s/ $//;              # 去尾
  $s =~ s/[^\w\s\.\,\-\/]/ /g;  # 非白名单字符替换为空格
  $s =~ s/ +/ /g;            # 再次折叠
  return $s;
$$ LANGUAGE plperl IMMUTABLE;

SELECT clean_text('  Hello   <world>!  a/b,c-d  ');
text
        clean_text
---------------------------
 Hello world ! a/b,c-d
(1 row)

示例二：从日志行抽取字段 ​

sql
CREATE OR REPLACE FUNCTION parse_log_line(line text,
                                           OUT ts text,
                                           OUT level text,
                                           OUT msg text)
RETURNS record AS $$
  my ($l) = @_;
  return if !defined $l;
  if ($l =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\s+\[(\w+)\]\s+(.*)$/) {
      ($ts, $level, $msg) = ($1, $2, $3);
  } else {
      ($ts, $level, $msg) = (undef, 'UNKNOWN', $l);
  }
  return;
$$ LANGUAGE plperl IMMUTABLE;

SELECT * FROM parse_log_line('2026-09-28 10:15:33 [ERROR] connection reset by peer');
text
         ts          | level |          msg
---------------------+-------+-----------------------
 2026-09-28 10:15:33 | ERROR | connection reset by peer
(1 row)

示例三：批量构造 SQL 条件 ​

用 quote_ident / quote_literal 安全拼装 SQL，避免注入：

sql
CREATE OR REPLACE FUNCTION build_filter(tbl text, col text, val text)
RETURNS text AS $$
  my ($t, $c, $v) = @_;
  return sprintf('SELECT * FROM %s WHERE %s = %s',
                 quote_ident($t), quote_ident($c), quote_nullable($v));
$$ LANGUAGE plperl IMMUTABLE;

SELECT build_filter('orders', 'status', 'paid');
text
                       build_filter
----------------------------------------------------------
 SELECT * FROM orders WHERE status = 'paid'
(1 row)

示例四：用 SPI 做数据迁移 ​

sql
CREATE OR REPLACE FUNCTION upper_all_names() RETURNS integer AS $$
  my $rv = spi_exec_query('SELECT id, name FROM users WHERE name IS NOT NULL');
  my $n = $rv->{processed};
  for my $i (0 .. $n - 1) {
      my $row = $rv->{rows}[$i];
      my $up  = uc($row->{name});
      my $q   = quote_literal($up);
      spi_exec_query("UPDATE users SET name = $q WHERE id = $row->{id}");
  }
  return $n;
$$ LANGUAGE plperl;

逐行 UPDATE 效率低

上面的写法可读性好，但每行一次 SQL。数据量大时应改用单条 UPDATE + 正则表达式，或 SQL 层的 upper()。PL/Perl 适合的是「SQL 写不出来的复杂逻辑」。

示例五：游标流式处理大表 ​

sql
CREATE OR REPLACE FUNCTION md5_all() RETURNS bigint AS $$
  my $count = 0;
  my $sth = spi_query('SELECT payload FROM events');
  while (defined (my $row = spi_fetchrow($sth))) {
      my $digest = sprintf('%x', 0);  # 占位：真实计算略
      $count++;
  }
  return $count;
$$ LANGUAGE plperl;

spi_fetchrow 返回 undef 时游标会自动释放；若中途 return，务必先调 spi_cursor_close($sth)。

示例六：return_next 实现集合返回 ​

sql
CREATE TYPE kv AS (k text, v text);

CREATE OR REPLACE FUNCTION split_pairs(input text)
RETURNS SETOF kv AS $$
  my $s = shift;
  return undef if !defined $s;
  for my $pair (split /;/, $s) {
      my ($k, $v) = split /=/, $pair, 2;
      return_next({ k => $k, v => $v });
  }
  return undef;
$$ LANGUAGE plperl;

SELECT * FROM split_pairs('a=1;b=2;c=3');
text
 k | v
---+---
 a | 1
 b | 2
 c | 3
(3 rows)

示例七：触发器强制字段规范 ​

sql
CREATE TABLE tickets (
  id      serial PRIMARY KEY,
  subject text,
  tag     text
);

CREATE OR REPLACE FUNCTION normalize_ticket() RETURNS trigger AS $$
  $_TD->{new}{subject} =~ s/\s+/ /g;
  $_TD->{new}{subject} =~ s/^ +| +$//g;
  $_TD->{new}{tag} = lc($_TD->{new}{tag}) if defined $_TD->{new}{tag};
  return "MODIFY";
$$ LANGUAGE plperl;

CREATE TRIGGER trg_normalize_ticket
  BEFORE INSERT OR UPDATE ON tickets
  FOR EACH ROW EXECUTE FUNCTION normalize_ticket();

INSERT INTO tickets (subject, tag) VALUES ('  Disk   Full!  ', 'OPS');
SELECT * FROM tickets;
text
 id |   subject   | tag
----+-------------+-----
  1 | Disk Full!  | ops
(1 row)

示例八：用 %_SHARED 缓存编译后的正则 ​

正则编译有一定开销，把 qr// 对象缓存进 %_SHARED 可以复用：

sql
CREATE OR REPLACE FUNCTION is_valid_code(code text) RETURNS boolean AS $$
  $_SHARED{re} ||= qr/^[A-Z]{2}-\d{4}$/;
  return undef if !defined $code;
  return ($code =~ $_SHARED{re}) ? 1 : 0;
$$ LANGUAGE plperl IMMUTABLE;

SELECT is_valid_code('AB-1234'), is_valid_code('bad');
text
 is_valid_code | is_valid_code
---------------+---------------
 t             | f
(1 row)

注意事项 ​

  • 'f' 在 Perl 里是真值。 bool 参数默认以文本传入，直接 if ($_[0]) 判断会出错。必须用 bool_plperl 的 TRANSFORM FOR TYPE bool，或显式比较 eq 't'。

    sql
    -- 错误！
CREATE FUNCTION f(bool) RETURNS text AS $$
  return $_[0] ? 'yes' : 'no';   -- false 时也返回 'yes'
$$ LANGUAGE plperl;

-- 正确
CREATE FUNCTION f(bool) RETURNS text
TRANSFORM FOR TYPE bool AS $$
  return $_[0] ? 'yes' : 'no';
$$ LANGUAGE plperl;

  • NULL 参数是 undef，不是 0 也不是空串。 不判 defined 就会把 NULL 当 0 参与计算。优先加 STRICT。

  • 可信 plperl 不是安全沙箱。 官方明确说 Perl 的 Opcode 模块对此场景「不生效」。安全要求高时执行 REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC;。

  • spi_exec_query 会把整个结果集读进内存。 大表必须改用 spi_query / spi_fetchrow。

  • 中途退出游标循环必须 spi_cursor_close。 否则内存泄漏。

  • spi_exec_query 无法执行 COMMIT / ROLLBACK。 必须用 spi_commit() / spi_rollback()，且只能在过程或顶层 DO 块里。

  • spi_commit() / spi_rollback() 不能在普通函数里调用。

  • spi_prepare 的 SQL 字符串要用单引号。 双引号会让 Perl 试图插值 $1、$2。

  • PL/Perl 函数不能互相直接调用。 想复用只能靠 %_SHARED 存代码引用，或复制代码。

  • %_SHARED 不跨 SQL 角色共享。 用 SECURITY DEFINER + 同一属主可解决。

  • 不要写「命名的嵌套子程序」。 官方警告：The use of named nested subroutines is dangerous in Perl, especially if they refer to lexical variables in the enclosing scope. 应改用匿名子程序 + coderef。

  • $_TD->{relname} 已弃用。 用 table_name。

  • 行级触发器以外的触发器没有 NEW / OLD。 语句级触发器访问 $_TD->{new} 会得到空值。

  • 事件触发器没有 SKIP / MODIFY。 返回值被忽略，要阻断 DDL 只能在函数体里 elog(ERROR, ...)。

  • plperl.use_strict 只影响之后编译的函数。 当前会话里已编译的函数不受影响。

  • 会话结束只执行 END 块。 文件句柄不会自动 flush，对象不会自动析构。

  • 参数与返回值都经过编码转换。 官方原文：Arguments will be converted from the database's encoding to UTF-8 for use inside PL/Perl, and then converted from UTF-8 back to the database encoding upon return.

  • bytea 必须手动编解码。 用 decode_bytea / encode_bytea。

  • 返回列表要用引用。 官方原文：A PL/Perl function is called in a scalar context, so it can't return a list.

最佳实践 ​

  • 先判断是否需要 PL/Perl。 纯 SQL 能表达的、以集合运算为主的逻辑，交给 SQL 或 PL/pgSQL 更快。

  • 默认用可信 plperl，只在确有必要时用 plperlu。 需要发邮件、调用外部程序、读文件时才升级权限，并把这类函数集中放在一个受限 schema 里。

    sql
    CREATE SCHEMA IF NOT EXISTS plu;
REVOKE ALL ON SCHEMA plu FROM PUBLIC;

  • 可信语言的权限按需要收紧。

    sql
    REVOKE USAGE ON LANGUAGE plperl FROM PUBLIC;
GRANT  USAGE ON LANGUAGE plperl TO app_dev;

  • bool 一律加 TRANSFORM FOR TYPE bool，别靠隐式文本比较。

    sql
    CREATE EXTENSION IF NOT EXISTS bool_plperl;

  • 参数先判 defined，或直接标 STRICT。

    sql
    CREATE FUNCTION f(text) RETURNS text AS $$ ... $$ LANGUAGE plperl STRICT;

  • 大结果集一律走 spi_query / spi_fetchrow，集合函数一律走 return_next。

  • 用 quote_ident / quote_literal / quote_nullable 拼 SQL，不要手工加引号。

  • 用 elog(NOTICE, ...) 输出诊断信息，而不是 print。 print 的输出不会进数据库日志。

  • 把 use strict; 写进函数体或全局开启 plperl.use_strict。 能提前暴露大量拼写与作用域错误。

  • 给 PL/Perl 函数标上正确的波动性（IMMUTABLE / STABLE / VOLATILE）。 纯文本处理通常是 IMMUTABLE，能显著提升计划质量。

    sql
    CREATE FUNCTION clean_text(text) RETURNS text AS $$ ... $$ LANGUAGE plperl IMMUTABLE;

  • 正则预先编译并缓存进 %_SHARED。

  • 给 PL/Perl 函数单独放一个 schema，并严格控制 EXECUTE 授权。

    sql
    REVOKE ALL ON FUNCTION perl_tool.clean_text(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION perl_tool.clean_text(text) TO app_writer;

  • 把「数据库服务器上需要的 Perl 模块清单」纳入部署文档与镜像构建。 可信版无法 use 外部模块，只有 plperlu 才能加载，这经常在迁移时踩坑。

    bash
    perl -MDigest::MD5 -e 'print "Digest::MD5 ok\n"'

下一节

下一节：PL/Java / PL/v8 简介——走出官方发行版，看看 Java 与 JavaScript 过程语言能带来什么，以及它们的安装成本与适用边界。

参考链接 ​