Postgresql 中文操作指南

45.1. PL/Perl Functions and Arguments #

要在 PL/Perl 语言中创建函数，请使用标准 CREATE FUNCTION 语法：

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

函数的代码主体是普通的 Perl 代码。实际上，PL/Perl 胶水代码会将它包装到 Perl 子例程内。PL/Perl 函数在标量环境中调用，因此它不能返回列表。可以通过返回引用（如下面所述）来返回非标量值（数组、记录和集）。

在 PL/Perl 过程中，Perl 代码的任何返回值都会被忽略。

PL/Perl 还支持使用 DO 语句调用的匿名代码块：

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

匿名代码块不接收任何参数，并且它可能返回的任何值都会被丢弃。在其他方面，它的行为与函数完全一致。

Note

在 Perl 中使用命名的嵌套子例程是危险的，尤其是当它们引用封闭作用域中的词法变量时。因为 PL/Perl 函数封装在一个子例程中，所以您放在其中的任何命名子例程都将是嵌套的。通常，创建匿名子例程并通过 coderef 调用它们要安全得多。有关更多信息，请参阅 perldiag 手册页中的 Variable "%s" will not stay shared 和 Variable "%s" is not available 条目，或在互联网上搜索“perl 嵌套命名子例程”。

CREATE FUNCTION_命令的语法要求函数体写为字符串常量。通常最方便的是对字符串常量使用美元符号引用（参阅 Section 4.1.2.4）。如果你选择使用转义字符串语法_E''，你必须对函数体中使用的任何单引号（'）和反斜杠（\）加倍（参阅 Section 4.1.2.1）。

参数和结果的处理方式与任何其他 Perl 子例程的处理方式一致：参数在 @_ 中传递，并使用 return 或作为函数中计算的最后一个表达式来返回结果值。

例如，可以将返回两个整数值中较大的值的函数定义为：

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

Note

参数将从数据库的编码转换为 UTF-8，以便在 PL/Perl 内部使用，然后在返回时从 UTF-8 转换回数据库编码。

如果将一个 SQL null 值传递给函数，该参数值在 Perl 中将显示为“未定义”。上述函数定义没办法非常好地处理 null 输入（实际上，该函数会将它们当做零）。我们可以将 STRICT 添加到函数定义中，以使 PostgreSQL 执行更合理的操作：如果传递了 null 值，则根本不会调用该函数，而只是会自动返回一个 null 结果。或者，我们可以检查函数主体中的未定义输入。例如，假设我们希望 perl_max 使用一个 null 参数和一个非空参数来返回非空参数，而不是 null 值：

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;

如上所示，要从 PL/Perl 函数返回一个 SQL null 值，请返回一个未定义的值。无论函数是否严格，都可以执行此操作。

函数参数中不是引用的任何内容都是字符串，该字符串以相关数据类型的标准 PostgreSQL 外部文本表示形式给出。在普通数字或文本类型的情况下，Perl 只需执行正确的操作，因此程序员通常不用担心它。然而，在其他情况下，需要将参数转换为在 Perl 中更易于使用的形式。例如，可以将 decode_bytea 函数用于将类型为 bytea 的参数转换为未转义的二进制。

类似地，传回 PostgreSQL 的值必须采用外部文本表示形式。例如，可以使用 encode_bytea 函数针对类型为 bytea 的返回值转义二进制数据。

布尔值是一个特别重要的案例。正如已经陈述的， bool 值的默认行为是将它们作为文本传递给 Perl，因此可以是 't' 或 'f' 。这是成问题的，因为 Perl 不将 'f' 视为 false！可以通过使用“转换”（见 CREATE TRANSFORM ）来改进问题。 bool_plperl 扩展提供了合适的转换。要使用它，请安装该扩展：

CREATE EXTENSION bool_plperl;  -- or bool_plperlu for PL/PerlU

然后，对接受或返回 bool 的 PL/Perl 函数使用 TRANSFORM 函数属性，例如：

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

当应用此转换时，_bool_参数将被Perl视为_1_或空，因此正确为真或假。如果函数结果是_bool_类型，它将根据Perl将返回的值评估为真或假。还对函数内部执行的布尔查询参数和SPI查询的结果执行类似的转换（ Section 45.3.1）。

Perl 可以返回 PostgreSQL 数组，作为对 Perl 数组的引用。这里有一个示例：

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

select returns_array();

Perl 将 PostgreSQL 数组传递为一个成功的 PostgreSQL::InServer::ARRAY 对象。此对象可以被视为数组引用或字符串，从而允许向后兼容为低于 9.1 版本的 PostgreSQL 编写的 Perl 代码的运行。例如：

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;

SELECT concat_array_elements(ARRAY['PL','/','Perl']);

Note

多维数组表示为对低维数组的引用，引用方式是每个 Perl 程序员的通用方式。

复合类型参数作为对散列的引用传递给函数。散列的键是复合类型的属性名称。这里有一个示例：

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;

SELECT name, empcomp(employee.*) FROM employee;

PL/Perl 函数可以使用相同的方法来返回复合类型结果：返回一个对散列的引用，该散列具有所需的属性。例如：

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;

SELECT * FROM perl_row();

已声明结果数据类型中不存在于散列中的任何列都将作为 null 值返回。

同样，过程的输出参数可以作为哈希引用返回：

CREATE PROCEDURE perl_triple(INOUT a integer, INOUT b integer) AS $$
    my ($a, $b) = @_;
    return {a => $a * 3, b => $b * 3};
$$ LANGUAGE plperl;

CALL perl_triple(5, 10);

PL/Perl 函数还可以返回标量类型或复合类型的集合。通常，你需要每次返回一行，以加快启动时间并避免将整个结果集排队到内存中。你可以通过 return_next 完成此功能，如下所示。请注意，在最后一个 return_next 之后，你必须放置 return 或（更好）return undef。

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

SELECT * FROM perl_set_int(5);

CREATE OR REPLACE FUNCTION perl_set()
RETURNS SETOF testrowperl AS $$
    return_next({ f1 => 1, f2 => 'Hello', f3 => 'World' });
    return_next({ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' });
    return_next({ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' });
    return undef;
$$ LANGUAGE plperl;

对于较小的结果集，你可以返回一个对数组的引用，该数组包含标量、对数组的引用或对哈希的引用，分别表示简单类型、数组类型和复合类型。以下是返回整个结果集作为数组引用的几个简单示例：

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

SELECT * FROM perl_set_int(5);

CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
    return [
        { f1 => 1, f2 => 'Hello', f3 => 'World' },
        { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
        { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
    ];
$$ LANGUAGE plperl;

SELECT * FROM perl_set();

如果你希望在代码中使用 strict 编译指示，则你有一些选项。对于临时全局使用，你可以 SET plperl.use_strict 为 true。这将影响 PL/Perl 函数的后续编译，但不影响当前会话中已编译的函数。对于永久全局使用，你可以 plperl.use_strict 为 true。

对于在特定函数中永久使用，你可以简单地放置：

use strict;

于函数体的顶部。

如果你的 Perl 是 5.10.0 或更高版本，也可以使用 feature 编译指示 use。