Postgresql 中文操作指南

44.2. PL/Tcl Functions and Arguments #

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

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

CREATE FUNCTION funcname (argument-types) RETURNS return-type AS $$
    # PL/Tcl function body
$$ LANGUAGE pltcl;

PL/TclU 相同，除了必须将语言指定为 pltclu。

PL/TclU is the same, except that the language has to be specified as pltclu.

该函数的主体只是一段 Tcl 脚本。在调用该函数时，参数值将作为名为 ` 1 ` … ` n ` 的变量传递给 Tcl 脚本。结果通过 ` return ` 语句以通常的方式从 Tcl 代码中返回。在过程中，将忽略来自 Tcl 代码的返回值。

The body of the function is simply a piece of Tcl script. When the function is called, the argument values are passed to the Tcl script as variables named 1 … n. The result is returned from the Tcl code in the usual way, with a return statement. In a procedure, the return value from the Tcl code is ignored.

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

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

CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl STRICT;

请注意，子句 STRICT 会省去我们对空输入值进行思考的步骤：如果传递了一个空值，我们将完全不会调用该函数，而只是自动返回空结果。

Note the clause STRICT, which saves us from having to think about null input values: if a null value is passed, the function will not be called at all, but will just return a null result automatically.

在非严格函数中，如果某个参数的实际值为 null，则对应的 $_n_ 变量将设置为一个空字符串。若要检测某个特定参数是否为 null，请使用函数 argisnull 。例如，假设我们希望 tcl_max 带有一个 null 和一个非 null 参数返回非 null 参数，而不是 null：

In a nonstrict function, if the actual value of an argument is null, the corresponding $_n_ variable will be set to an empty string. To detect whether a particular argument is null, use the function argisnull. For example, suppose that we wanted tcl_max with one null and one nonnull argument to return the nonnull argument, rather than null:

CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
    if {[argisnull 1]} {
        if {[argisnull 2]} { return_null }
        return $2
    }
    if {[argisnull 2]} { return $1 }
    if {$1 > $2} {return $1}
    return $2
$$ LANGUAGE pltcl;

如上所示，要从 PL/Tcl 函数返回一个空值，请执行 return_null。无论该函数是否严格，这一步都是可行的。

As shown above, to return a null value from a PL/Tcl function, execute return_null. This can be done whether the function is strict or not.

复合类型参数作为 Tcl 数组传递给该函数。该数组的元素名称是复合类型的属性名称。如果传递的行中的某个属性具有空值，则它不会显示在数组中。这里提供一个示例：

Composite-type arguments are passed to the function as Tcl arrays. The element names of the array are the attribute names of the composite type. If an attribute in the passed row has the null value, it will not appear in the array. Here is an example:

CREATE TABLE employee (
    name text,
    salary integer,
    age integer
);

CREATE FUNCTION overpaid(employee) RETURNS boolean AS $$
    if {200000.0 < $1(salary)} {
        return "t"
    }
    if {$1(age) < 30 && 100000.0 < $1(salary)} {
        return "t"
    }
    return "f"
$$ LANGUAGE pltcl;

PL/Tcl 函数也可以返回复合类型的结果。要做到这一点，Tcl 代码必须返回一个列名/值对列表，与预期的结果类型相匹配。列表中遗漏的任何列名会返回为 null，并且，如果出现了意外的列名，则会引发一个错误。这里提供了一个示例：

PL/Tcl functions can return composite-type results, too. To do this, the Tcl code must return a list of column name/value pairs matching the expected result type. Any column names omitted from the list are returned as nulls, and an error is raised if there are unexpected column names. Here is an example:

CREATE FUNCTION square_cube(in int, out squared int, out cubed int) AS $$
    return [list squared [expr {$1 * $1}] cubed [expr {$1 * $1 * $1}]]
$$ LANGUAGE pltcl;

以相同的方式返回过程的输出参数，例如：

Output arguments of procedures are returned in the same way, for example:

CREATE PROCEDURE tcl_triple(INOUT a integer, INOUT b integer) AS $$
    return [list a [expr {$1 * 3}] b [expr {$2 * 3}]]
$$ LANGUAGE pltcl;

CALL tcl_triple(5, 10);

Tip

可以使用 array get Tcl 命令，基于所需元组的数组表示，来生成结果列表。例如：

The result list can be made from an array representation of the desired tuple with the array get Tcl command. For example:

CREATE FUNCTION raise_pay(employee, delta int) RETURNS employee AS $$
    set 1(salary) [expr {$1(salary) + $2}]
    return [array get 1]
$$ LANGUAGE pltcl;

PL/Tcl 函数可以返回集合。要执行此操作，Tcl 代码应针对要返回的每行调用 return_next 一次，在返回标量类型时传递相应的值，或者在返回复合类型时传递列名/值对列表。这里提供一个返回标量类型的示例：

PL/Tcl functions can return sets. To do this, the Tcl code should call return_next once per row to be returned, passing either the appropriate value when returning a scalar type, or a list of column name/value pairs when returning a composite type. Here is an example returning a scalar type:

CREATE FUNCTION sequence(int, int) RETURNS SETOF int AS $$
    for {set i $1} {$i < $2} {incr i} {
        return_next $i
    }
$$ LANGUAGE pltcl;

下面是一个返回复合类型的示例：

and here is one returning a composite type:

CREATE FUNCTION table_of_squares(int, int) RETURNS TABLE (x int, x2 int) AS $$
    for {set i $1} {$i < $2} {incr i} {
        return_next [list x $i x2 [expr {$i * $i}]]
    }
$$ LANGUAGE pltcl;