Now, let's write our first simple Tcl function to make sure that PL/Tcl is installed. We will write a simple factorial calculation function, as shown here:
CREATE OR REPLACE FUNCTION tcl_factorial(integer) RETURNS integer AS $$ set i 1; set fact 1 while {$i <= $1} { set fact [expr $fact * $i] incr i } return $fact $$ LANGUAGE pltcl STRICT;
This function calculates the factorial of a number in an iterative way. Let's try and run it:
postgres=# SELECT tcl_factorial(5); tcl_factorial --------------- 120 (1 row)
It works and the function looks similar to other functions we have been writing in PL/pgSQL and PL/Python. The CREATE FUNCTION
statement creates a function. It needs a name, function argument type list (you have to use parentheses, even if there are no arguments), a result type, and a language.
The body of the function is just a Tcl script. PostgreSQL passes the body on to a Tcl interpreter to run this subroutine and return the results. The function arguments are passed on to the script as $1
, $2
,…$n
.
The
STRICT
keyword will save us from checking the null
input parameters. If you have specified a function as STRICT
and any of the input parameters are null, it results in the function not being called and a null result set is returned immediately:
postgres=# SELECT tcl_factorial(null); tcl_factorial --------------- (1 row)
If you don't want to create a STRICT
function, or you'd like to do the null checking yourself, you can rewrite the function, as shown in the following code snippet. This is useful if you have multiple parameters and you want to allow some parameters to be null:
CREATE OR REPLACE FUNCTION tcl_factorial_ns(integer) RETURNS integer AS $$ if {[argisnull 1]} { elog NOTICE "input is null" return -1 } set i 1; set fact 1 while {$i <= $1} { set fact [expr $fact * $i] incr i } return $fact $$ LANGUAGE pltcl;
The argisnull
function is used to check for null values. In the preceding example, the function returns -1
if the input argument is null, just to demonstrate that it works. If you want to return a null value from the function, you can use the built-in function return_null
. In the preceding example, you can also see how to use the elog
function in PL/Tcl:
postgres=# SELECT tcl_factorial_ns(null); tcl_factorial_ns ------------------ -1 (1 row)
All input parameters passed to PL/Tcl are converted to text. Within a PL/Tcl function, all values are text. When the function returns, another conversion is performed from the text string to the return type of the function, as long as the text being returned is an appropriate representation of the return type of the Pl/Tcl function; otherwise, the function will result in an error.
3.128.198.21