'''pltcl''' is a [PostgreSQL] module which embeds a [safe] Tcl interpreter ''inside'' the running database. You can write triggers, stored procedures, perform queries and so on.

There is a related '''pltclu''' ''unsafe'' module which allows access to the file system, sockets etc.

Here's an example:

  CREATE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
      if {$1 > $2} {
          return $1
      }
      return $2
  $$ LANGUAGE pltcl STRICT;

Everything between the ''$$'' quotes is Tcl. Arguments are positional and are 1-indexed.


'''Documentation:'''

   * http://www.postgresql.org/docs/8.3/interactive/pltcl.html

----

** Exploring the postgres tcl environment **

I would like to add some information that is missing from the Postgresql documentation. 

So we start with a working postgres server, are working interactively in '''psql''' logged into database projectone, and write the first script: (terminal protocol)

======
projectone=# create function test1() returns text as $$
projectone$#  return "okay, it works."
projectone$# $$ language pltcl;
ERROR:  language "pltcl" does not exist
TIP:  Use CREATE LANGUAGE to load the language into the database.
======
The message says it all. See the original pg documentation. But it is very easy, if you have installed the extension ( postgresql-pltcl in openSuse):
======
projectone=# create language pltcl;
CREATE LANGUAGE
projectone=# create language pltclu;
CREATE LANGUAGE
======
The '''pltclu''' has OS access, for example sending mail, managing files etc, while all work inside the database can be done with '''pltcl'''.
======
projectone=# create function test1() returns text as $$
                return "okay, it works."
             $$ language pltcl;
CREATE FUNCTION
projectone=# select test1() as helloworld;
   helloworld
-----------------
 okay, it works.
(1 Zeile)

======
Fine. But where is my proc? I provoke a script error:
======
projectone=# create or replace function test1() returns text as $$
             whatisthis
             $$ language pltcl;
CREATE FUNCTION
projectone=# select test1() as helloworld;
ERROR:  invalid command name "whatisthis"
KONTEXT:  invalid command name "whatisthis"
    while executing
"whatisthis"
    (procedure "__PLTcl_proc_91665" line 3)
    invoked from within
"__PLTcl_proc_91665"
in PL/Tcl function "test1"
projectone=#
======

Just fine, it says ''(procedure "__PLTcl_proc_91665" line 3)'', so you see the procedures will get an internal name. Let's check the system tables:

======
projectone=# \x
Erweiterte Anzeige ist an.
projectone=# select * from pg_proc where oid=91665;
-[ RECORD 1 ]---+------------
proname         | test1
pronamespace    | 2200
proowner        | 16384
prolang         | 91662
<snip>
proargmodes     |
proargnames     |
proargdefaults  |
prosrc          |
                :  whatisthis
                :
probin          |
<snip>
======

There is your tcl proc, with the name in column proname, all the source in column prosrc.

How do I call my own proc?

I would never call the procedure directly, because it is set up to be called by a call handler. I do it like that - but first we have to fix the test1 proc:

======
projectone=# create or replace function test1() returns text as $$
           return "okay, just looking around."
           $$ language pltcl;
CREATE FUNCTION
projectone=# create or replace function test2(text) returns text as $$
             set parameter $1
             spi_exec "select test1() as hello"
             return "you said: $parameter, and test1 said $hello"
$$ language pltcl;
CREATE FUNCTION
projectone=# select test2('Does it work?');
                               test2
--------------------------------------------------------------------
 you said: Does it work?, and test1 said okay, just looking around.
(1 Zeile)

======

This example shows: how to use a parameter in your proc, how to execute sql, and how to use the result. 

Now let's explore the environment: (do not get confused by the different script delimiter, read the original doc)

======
projectone=# create or replace function eval_me(text) returns text as $_$
      return "[eval $1]"
      $_$ language pltcl;
CREATE FUNCTION
projectone=# select eval_me('info proc') as procedurelist;
                      procedurelist
----------------------------------------------------------
 __PLTcl_proc_91665 __PLTcl_proc_91666 __PLTcl_proc_91667
(1 Zeile)
======

This is the tool to further explore the pg environment.

You see, your procedures are there in the interpreter. They are valid for this database connection, and are defined after the first sql call, so subsequent calls can use the compiled proc.

Which variables do we have? See:

======
projectone=# select eval_me('info vars') as variables;
 variables
-----------
 1 GD
(1 Zeile)
======

We have variables 1 (the first argument), and GD which is mentioned in the docs as a global variable where you store your prepared sql statements. Do not do it like this, see later. 

Can we use global variables? Check it out using the namespace delimiter:

======
projectone=# select eval_me('set ::mysession "it is me, nessuno."') as oops;
        oops
--------------------
 it is me, nessuno.
(1 Zeile)

projectone=# select eval_me('info vars ::*') as variables;
                                              variables
-----------------------------------------------------------------------------------------------------
 ::tcl_interactive ::tcl_version ::errorCode ::mysession ::tcl_patchLevel ::errorInfo ::tcl_platform
(1 Zeile)

projectone=# select eval_me('set ::mysession') as variables;
     variables
--------------------
 it is me, nessuno.
(1 Zeile)
======

So there it is. And there are the '''error variables''' nobody could tell me about: ''::errorCode'' and ''::errorInfo'' reflect the sql state.

But mind you: one interpreter per db connection, an one for ''pltcl'', another for ''pltclu''.

*** How to signal errors to the calling program ***

The '''elog''' command is for this purpose (see pg docs). However the reaction to error conditions is configurable. Here is a little function to do the tests - we can raise any condition like WARNING or ERROR by function call:

======
projectone=# create or replace function testelog(text, text)  returns text as $_$
          elog $1 $2
         return "testing condition $1"
         $_$ language pltcl;
CREATE FUNCTION
projectone=# select testelog('ERROR', 'do not do that');
ERROR:  do not do that
KONTEXT:  do not do that
    while executing
"elog $1 $2"
    (procedure "__PLTcl_proc_91668" line 3)
    invoked from within
"__PLTcl_proc_91668 ERROR {do not do that}"
in PL/Tcl function "testelog"
projectone=#
======

More important than doing it like this is to test it with your client application to see how you get the messages. 

Here an '''example in PHP''' running interactively:

======
php >  $res = $DBH->query("select testelog('ERROR','thats a serious error')");
php > if ($res) print_r($res->fetchAll()); else echo "Statement empty";
Statement empty
php > print_r($DBH->errorInfo());
Array
(
    [0] => XX000
    [1] => 7
    [2] => ERROR:  thats a serious error
CONTEXT:  thats a serious error
    while executing
"elog $lev $msg"
    (procedure "__PLTcl_proc_65287" line 5)
    invoked from within
"__PLTcl_proc_65287 ERROR {thats a serious error}"
in PL/Tcl function "testelog"
)
======


*** How to query your tables directly ***

See above "call my own proc" for an example of spi_exec without a loop, and without an array. I think the array option is useful, so we test it here:

First we need a table with entries:


======
projectone=# create table table1 (id integer, name text);                     
CREATE TABLE                                                                  
projectone=# insert into table1 values (1,'Peter'),(2,'Paul'),(null,'Evil');
INSERT 0 3
======

Here is a code only test procedure, that will print out the array variable ''ARR''.

The loop code is adapted from ''parray'' found in every tcl distro.

======
create or replace function testquery1(text) returns text as $$
    set infostring ""
    spi_exec -array ARR $1 {
        append infostring "enter Loop: --------------------\n"
        set a "ARR"
        set maxl 0
        set names [lsort [array names ARR ]]
        foreach name $names {
            if {[string length $name] > $maxl} {
                set maxl [string length $name]
            }
        }
        set maxl [expr {$maxl + [string length $a] + 2}]
        foreach name $names {
            set nameString [format %s(%s) $a $name]
            append infostring "[format "%-*s = %s" $maxl $nameString $ARR($name)]\n"
        }
    }
    return $infostring
$$ language pltcl;
======

See what is defined when we execute this query - the ''.tupno'' saves you from managing counters:

======
projectone=# select testquery1('select * from table1');
            testquery1
----------------------------------
 enter Loop: --------------------
 ARR(.tupno) = 0
 ARR(id)     = 1
 ARR(name)   = Peter
 enter Loop: --------------------
 ARR(.tupno) = 1
 ARR(id)     = 2
 ARR(name)   = Paul
 enter Loop: --------------------
 ARR(.tupno) = 2
 ARR(name)   = Evil

(1 Zeile)
======

Maybe more important to note is what is not defined: ARR(id) in the third iteration is not defined!

Note: A null result from an sql query will lead to a undefined variable in pltcl! 

Would be nice if this was a configurable behavior. It is not, so you have to:
 * be shure not to have null result values
 * or test every result var with [info exists] before using it

*** How to prepare your query ***

todo ... coming soon, and better than the pg docs

*** How to use your tcl utitlity procs ***

todo ... coming soon, this is important

----

'''See Also:'''

   * [pgtcl] for access to the database from a stand-alone script.

----
!!!!!!
%| [Category PostgreSQL] | [Category Database] |%
!!!!!!