44.2. PL/Tcl Functions and Arguments

44.2. PL/Tcl Functions and Arguments
Prev	Up	Chapter 44. PL/Tcl — Tcl Procedural Language	Home	Next

+ 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 is the same, except that the language has to be specified as + pltclu. +

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

+ + 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. +

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

+ 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. +

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

Tip

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

Prev	Up	Next
44.1. Overview	Home	44.3. Data Values in PL/Tcl