Movatterモバイル変換

To create a function in thePL/Tcl language, use the standardCREATE FUNCTION syntax:

CREATE FUNCTIONfuncname (argument-types) RETURNSreturn-type AS $$    # PL/Tcl function body$$ LANGUAGE pltcl;

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

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 named1 ...n. The result is returned from the Tcl code in the usual way, with areturn 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;

Note the clauseSTRICT, 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.

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 functionargisnull. For example, suppose that we wantedtcl_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;

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

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 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);

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 functions can return sets. To do this, the Tcl code should callreturn_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;