Postgresql 中文操作指南

45.1. PL/Perl Functions and Arguments #

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

To create a function in the PL/Perl language, use the standard CREATE FUNCTION syntax:

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 函数在标量环境中调用，因此它不能返回列表。可以通过返回引用（如下面所述）来返回非标量值（数组、记录和集）。

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. You can return non-scalar values (arrays, records, and sets) by returning a reference, as discussed below.

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

In a PL/Perl procedure, any return value from the Perl code is ignored.

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

PL/Perl also supports anonymous code blocks called with the DO statement:

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.

Note

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

The use of named nested subroutines is dangerous in Perl, especially if they refer to lexical variables in the enclosing scope. Because a PL/Perl function is wrapped in a subroutine, any named subroutine you place inside one will be nested. In general, it is far safer to create anonymous subroutines which you call via a coderef. For more information, see the entries for Variable "%s" will not stay shared and Variable "%s" is not available in the perldiag man page, or search the Internet for “perl nested named subroutine”.

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

The syntax of the CREATE FUNCTION command requires the function body to be written as a string constant. It is usually most convenient to use dollar quoting (see Section 4.1.2.4) for the string constant. If you choose to use escape string syntax E'', you must double any single quote marks (') and backslashes (\) used in the body of the function (see Section 4.1.2.1).

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

Arguments and results are handled as in any other Perl subroutine: arguments are passed in @_, and a result value is returned with return or as the last expression evaluated in the function.

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

For example, a function returning the greater of two integer values could be defined as:

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 转换回数据库编码。

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.

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

If an SQL null value is passed to a function, the argument value will appear as “undefined” in Perl. The above function definition will not behave very nicely with null inputs (in fact, it will act as though they are zeroes). We could add STRICT to the function definition to make PostgreSQL do something more reasonable: if a null value is passed, the function will not be called at all, but will just return a null result automatically. Alternatively, we could check for undefined inputs in the function body. For example, suppose that we wanted perl_max with one null and one nonnull argument to return the nonnull argument, rather than a null value:

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 值，请返回一个未定义的值。无论函数是否严格，都可以执行此操作。

As shown above, 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.

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

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. In the case of ordinary numeric or text types, Perl will just do the right thing and the programmer will normally not have to worry about it. However, in other cases the argument will need to be converted into a form that is more usable in Perl. For example, the decode_bytea function can be used to convert an argument of type bytea into unescaped binary.

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

Similarly, values passed back to PostgreSQL must be in the external text representation format. For example, the encode_bytea function can be used to escape binary data for a return value of type bytea.

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

One case that is particularly important is boolean values. As just stated, the default behavior for bool values is that they are passed to Perl as text, thus either 't' or 'f'. This is problematic, since Perl will not treat 'f' as false! It is possible to improve matters by using a “transform” (see CREATE TRANSFORM). Suitable transforms are provided by the bool_plperl extension. To use it, install the extension:

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

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

Then use the TRANSFORM function attribute for a PL/Perl function that takes or returns bool, for example:

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）。

When this transform is applied, bool arguments will be seen by Perl as being 1 or empty, thus properly true or false. If the function result is type bool, it will be true or false according to whether Perl would evaluate the returned value as true. Similar transformations are also performed for boolean query arguments and results of SPI queries performed inside the function (Section 45.3.1).

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

Perl can return PostgreSQL arrays as references to Perl arrays. Here is an example:

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 代码的运行。例如：

Perl passes PostgreSQL arrays as a blessed PostgreSQL::InServer::ARRAY object. This object may be treated as an array reference or a string, allowing for backward compatibility with Perl code written for PostgreSQL versions below 9.1 to run. For example:

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 程序员的通用方式。

Multidimensional arrays are represented as references to lower-dimensional arrays of references in a way common to every Perl programmer.

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

Composite-type arguments are passed to the function as references to hashes. The keys of the hash are the attribute names of the composite type. Here is an example:

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 函数可以使用相同的方法来返回复合类型结果：返回一个对散列的引用，该散列具有所需的属性。例如：

A PL/Perl function can return a composite-type result using the same approach: return a reference to a hash that has the required attributes. For example:

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 值返回。

Any columns in the declared result data type that are not present in the hash will be returned as null values.

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

Similarly, output arguments of procedures can be returned as a hash reference:

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。

PL/Perl functions can also return sets of either scalar or composite types. 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. You can do this with return_next as illustrated below. Note that after the last return_next, you must put either return or (better) 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;

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

For small result sets, you can return a reference to an array that contains either scalars, references to arrays, or references to hashes for simple types, array types, and composite types, respectively. Here are some simple examples of returning the entire result set as an array reference:

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。

If you wish to use the strict pragma with your code you have a few options. For temporary global use you can SET plperl.use_strict to true. This will affect subsequent compilations of PL/Perl functions, but not functions already compiled in the current session. For permanent global use you can set plperl.use_strict to true in the postgresql.conf file.

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

For permanent use in specific functions you can simply put:

use strict;

于函数体的顶部。

at the top of the function body.

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

The feature pragma is also available to use if your Perl is version 5.10.0 or higher.