PLTcl

I l @ ve RuBoard

PL/Tcl

The PL/Tcl language allows a trusted version of the popular Tool Command Language (Tcl) to be used when creating custom functions or triggers in PostgreSQL. Although a full explanation of the Tcl language is outside the scope of this book, we will highlight some of the major features and provide some examples.

The major difference between the regular Tcl language and PL/Tcl is that the latter is running in a trusted mode. This means that no OS-level activity can be performed. Moreover, only a limited set of Tcl commands are enabled. In fact, Tcl functions cannot be used to create new data types in PostgreSQL.

If OS-level operations are desired, there is a more expressive version of PL/Tcl available named PL/TclU (Tcl Untrusted) that can be used for programming in PostgreSQL. By default, this language is not available in the base distribution and must explicitly be added to be operational. The reader is urged caution, however, because errant scripts could cause system corruption or failure.

General Tcl Language Primer

Much of the syntax in PL/Tcl is the same as Tcl in general. The following is a brief synopsis of how to use Tcl.

Comments

Like many scripting languages, the default comment indicator is the pound sign (#). Any line that begins with this symbol is ignored entirely. For instance:

 CREATE FUNCTION addit (arg1) RETURNS INTEGER AS '             # Set variable to arg1             Set myvar              # This is another comment  ' LANGUAGE 'pltcl'; 

Variable Assignment

Tcl accepts variable assignments. For instance, to assign a variable, you could do the following:

 Set  myval  10  Set  mystr  "This is my string"  Set  myval_2  myval+100 

The first two examples are obvious: The variable myval is set to a numerical value of 10, and the variable mystr is set to string . However, the last example is deceptive. On first look, it would appear that the variable myval_2 should be equal to 110, but actually it is equal to the string myval+100 . To perform variable substitution, use the following syntax:

 Set myval_2 [expr $myval+100] 

PL/Tcl uses the $ symbol to indicate that a variable is being referenced. Additionally, anything enclosed in brackets ([]) is evaluated as Tcl code.

Control Structures

Like all modern scripting languages, Tcl has the standard flow-control mechanisms for determining code- path execution. For instance, the standard IF block looks like this:

 if {conditional-expression} {       #code block  } 

Tcl also supports IF ELSE control structures, such as:

 if {conditional-expression} {       #code block  }  else {       #something else  } 

Tcl also supports the standard WHILE and FOR loops . For instance:

 while {$x < 100} {       #some code        incr x 1  }  #loop has exited - run more code 

Or, alternatively, a FOR loop could be used. The FOR loop takes the following syntax:

 for {initial condition} {test condition} {modification} {      #some code  } 

For instance:

 for {set x 0} {$x < 100} {incr x 1} {       #some code  }  #loop has exited - run more code 

The preceding uses the incr Tcl command, which increments the variable specified with the given amount. (Note: 1 is the default; it does not need to be explicitly given.)

The Tcl language also supports a more powerful FOR loop called FOREACH . The basic syntax is as follows :

 foreach variable(s) list(s){      #some code  } 

For example:

 foreach month {Apr May Jun} {      #Run quarterly report  } 

Additionally, more complex FOREACH structures can be created by using multiple variable names and lists. For example:

 foreach xpoint ypoint {10 200 20 400} {      #On first run xpoint=10 , ypoint=200       #On second run xpoint=20 , ypoint=400  } 

Or alternatively:

 foreach xpoint {10, 200} ypoint {20 400} {      #On first run xpoint=10 , ypoint=200       #On second run xpoint=20 , ypoint=400  } 

Tcl also supports the SWITCH control structure. The basic syntax is as follows:

 switch option test-expression {  test_case1  {  code statement  }  test_case2  {  code statement  }  default {default code statement}  } 

OPTION usually refers to -exact , -glob , or -regexp , which does exact matching, pattern matching, or regular expression matching on the supplied test cases.

The DEFAULT keyword can be used to match a case that fails all other comparison tests. Additionally, a "-" sign as a code statement will indicate that the first following full code statement is to be run as the appropriate execution initiative. For instance:

 set myvar "Barry"  switch -glob $myvar {      arry {puts 1}       *arry -      Ba* -      Bar* {puts 4}       default {puts "Not Barry"}  } 

The preceding example will return a 4. Notice how the "-" continuation symbols can be linked together to form a chain of correct matches.

Strings and List

Tcl has many included list- and string- related commands. A brief listing is included here:

Command	Description
list {1 2 3 4}	Returns a list of supplied elements.
concat {1 2} {3 4}	Returns a concatenated list of elements.
lappend {1 2} {3 4}	Adds the elements to the list.
lindex {1 2 3} 2	Returns the Nth element specified (start=0).
linsert {1 2 4} 2 {3}	Inserts an item at the index point.
join {1 {2 3} 4}	Joins all elements into a single flat element.
llength {1 2 3 4}	Returns the length (elements) of the list.
lreplace {1 2 3} 1 2 a	Replaces elements at index 1 to 2 with an "a".
lsearch {a b c} b	Returns the index (that is, 1) of the searched.value "b".
lsort {b z a c}	Returns the list sorted.
split this,is,split ,	Splits the elements according to a supplied delimiter (a comma in this case).

PL/Tcl Language Specifics

Up to this point, we have been discussing general features of the Tcl language; Pl/Tcl adds some specific functionality to the base language.

Basic Structure

The following is the basic format of the PL/Tcl language:

 CREATE FUNCTION function_name (arg1 [, argN]) RETURNS type       AS '            # PL/tcl Code       ' LANGUAGE 'pltcl'; 

This is similar to how all PLs are used within PostgreSQL, and as with PL/pgSQL, care must be taken to properly escape quoted character strings correctly.

Arguments passed to PL/Tcl start at $1 and progress sequentially, as in PL/pgSQL. PL/Tcl will also accept arguments in the form of arrays. The passed array usually will refer to the specific element needed by using its attribute name . For instance:

 CREATE FUNCTION ispaid(payroll_array) RETURNS INTEGER       AS '             if {(salary)>0}{                 return 1             }             if {(hourly)>0}{                 return 1             }             return 0       ' LANGUAGE 'pltcl'; 

Global Data (GD) Directives

Due to the nature of performing queries with PL/Tcl, it is important to be able to store globally accessible data between various operations inside a PL/Tcl code block.

To accomplish this, PL/Tcl uses an internally available array named "GD." This variable is the recommended method for distributing shared information throughout a procedure. (See the example in the next section for a procedure that uses the GD variable.)

Accessing Data from PL/Tcl

Unlike PL/pgSQL, you cannot simply embed standard SQL statements inside of PL/Tcl. There are special built-in commands that allow access to the database back end.

Executing a Query Directly

The spi_exec command can be used to submit a query directly to the database query engine. The syntax for the spi_exec command is as follows:

 spi_exec -  options query  {  loop-statements  }  options               -    One of the following options:                             -count  n  Return only N rows from                              query                             -array  name  Stores results in                              associative array with given name  query                 -    The query string to execute  loop-statements       -    Execute these commands for                             each row returned 

The following are some examples of how the spi_exec command works:

 spi_exec "SELECT * FROM authors"  spi_exec -count 10 "SELECT * FROM authors ORDER BY name"  spi_exec -array myrecs "SELECT * FROM authors" 

Preparing and Executing a Query

The preceding use of spi_exec executed the queries by submitting them directly to the query engine. In many cases, this approach will work fine. However, if you plan to execute the same basic query multiple times ”with perhaps just a change in criteria ” it is more efficient to prepare the query and then execute it.

When a query is prepared, it is submitted to the query planner, which then prepares and saves a query plan for the submitted entry. It is then possible to use that query plan to execute the actual query, which can result in performance increases if used correctly.

A query is prepared by using the spi_prepare command, which takes the following syntax:

 spi_prepare  query typelist  query      - The SQL query to execute  typelist   - If arguments are going to be passed to the query               from the PL/Tcl code, then a listing of their data               types must be supplied 

The following is an example of the spi_prepare command. Notice the use of a double backslash to properly escape the $ symbol.Additionally, notice that the VARCHAR data type is supplied because of the $1 PL/Tcl variable:

 spi_prepare "SELECT * FROM authors WHERE name=\" VARCHAR 

After a query has been prepared, it can be executed with the spi_execp command. This command is similar to the spi_exec command, with the exception that is geared toward executing already-prepared queries. The following is the syntax that the spi_execp command uses:

 spi_execp  options queryID value-list  {  loop-statements  }  options         -     One of the following options;                        -count  n  Return only N rows from the query                        -array  name  Store the results in                         associative array with given name                        -nulls  str  Uses named string values to use                         all null values  queryID         -     The query OID returned from spi_prepare  value-list      -     If a  typelist  was provided to                        spi_prepare, then a list of those values                        must be supplied to spi_execp  loop-statement  -     A PL/Tcl statement that will be                        executed for every row returned 

The following is an example of using the spi_execp command; notice the use of the GD global system variable. In particular, the following example will only create the query plan when first called; on all subsequent calls, the previously saved plan is simply executed:

 CREATE FUNCTION count_checks(int4) RETURNS int4 AS '       #Check to see if plan exists       if {! [ info exists GD((plan) ]} {           set GD(plan) [ spi__prepare "SELECT count(*) AS            chk_count FROM payroll WHERE emp_id=\" int 4 ]       }       #Plan has been created or already exists       spi_execp $GD(plan) [ list $ ]       return $chk_count  ' LANGUAGE 'pltcl'; 

Constructing Queries

A related command that is useful when accessing the PostgreSQL back end is the quote statement. This command is useful in constructing query strings that make use of variable substitution.An example of the quote command is as follows:

 set myval "Barry"  quote "SELECT * FROM authors WHERE name=$myval" 

The preceding would result in the following text if sent to the query parser:

 "SELECT * FROM authors WHERE name='Barry'" 

One subtle point to watch out for is when the value of a variable already contains a single or double quote. The quote command will dutifully reproduce this, which could result in an error being generated from the PostgreSQL query parser. Consider the following:

 set myval 'Barrys'"  quote "SELECT * FROM authors WHERE name=$myval" 

The preceding would result in the following text if sent to the query parser:

 "SELECT * FROM authors WHERE name='Barry's" 

To correct this obvious problem, use the following syntax:

 set myval "Barrys'"  "SELECT * FROM authors WHERE name='[quote $myval ]'" 

Accessing the PostgreSQL Log System

Like PL/pgSQL, there are commands present in PL/Tcl that provide access to the PostgreSQL log system. The elog command uses the following syntax:

 elog  level message  level      -    Either NOTICE, ERROR, FATAL, DEBUG, or NOIND  message    -    The text message to pass to the log 

(For more information on the elog levels previously mentioned, refer to the elog C function discussed in Chapter 13, "Client-Side Programming.")

Notes

When installing PL/Tcl ”whether at compile time or after ”it is required that the Tcl language and associated libraries exist on the target system for installation to be successful.

I l @ ve RuBoard