A simple PL/Tcl function

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.

Null checking with Strict functions

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)

The parameter format

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.128.198.21