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:
The original postgresql doc is very good and quite complete. But especially the pl/tcl-part is quite short, and has not changed for some releases. I would like to add some information and examples that I hope will be useful to get started.
Required knowledge: some tcl, some sql, how to start psql, how to connect to your database.
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.
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"
)Here an example in Tcl running interactively:
str@u-s-dev1:/srv/www/default/scripts$ tclsh
% package require Pgtcl
proc get_pg_connection {} {
global DBH
if {![info exists DBH(handle)]} {
set localdb {dbname=projectone password=yesthereisone }
set DBH(handle) [pg_connect -conninfo $localdb]
}
return $DBH(handle)
}
proc pg_query_list {sql} {
global lastdbinfo
set dbresult [pg_exec [get_pg_connection] $sql]
set lastdbinfo [debug_pg_result $dbresult] ;# global
set temp [$dbresult -list]
$dbresult -clear
return $temp
}
proc debug_pg_result {rh} {
dict set retval status [pg_result $rh -status]
dict set retval error [pg_result $rh -error]
dict set retval severity [pg_result $rh -error severity]
dict set retval sqlstate [pg_result $rh -error sqlstate]
dict set retval primary [pg_result $rh -error primary]
dict set retval detail [pg_result $rh -error detail]
dict set retval numTuples [pg_result $rh -numTuples ]
dict set retval cmdTuples [pg_result $rh -cmdTuples ]
return $retval
}
1.8
% % % % %
% pg_query_list "select * from table1"
1 Peter 2 Paul {} Evil
% pg_query_list "select testelog('ERROR','we cause an error');"
% set lastdbinfo
status PGRES_FATAL_ERROR error {ERROR: we cause an error
CONTEXT: we cause an error
while executing
"elog $1 $2"
(procedure "__PLTcl_proc_91668" line 3)
invoked from within
"__PLTcl_proc_91668 ERROR {we cause an error}"
in PL/Tcl function "testelog"
} severity ERROR sqlstate XX000 primary {we cause an error} detail {} numTuples 0 cmdTuples {}
%Here I first load the helper procedures I am using, then do a select from table1 (defined below) to show the good case, then I cause a sql error.
We can see: for the client the result set is empty, error info is readily available using the pg_result options.
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. The rest is just spi_exec -array ARR "select ...":
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 an undefined variable in pltcl!
Would be nice if this was a configurable behavior. It is not, so you have to:
As expected the loop is not executed if there are no result tuples:
projectone=# select testquery1('select * from table1 where id>100');
testquery1
------------
(1 Zeile)str: Impossible. If you can do it please tell me (Name misterherr Mailserver freenet.de).
You could work around by using temp tables that your function fills, or return strings that can be split easily.
You must load your procs in the interpreter. Since there is no file system access in pltcl the source must be in the database. No problem.
As example we use the print array function and tickle it a bit to return the string instead of printing it: (code only)
create or replace function tclutil() returns text as $$
proc rarray {a {pattern *}} {
# RCS: @(#) $Id: parray.tcl,v 1.4 2005/06/03 10:02:23 dkf Exp $
# tickled 20110316-1615-strobel
upvar 1 $a array
if {![array exists array]} {
error "\"$a\" isn't an array"
}
set returnthis ""
set maxl 0
set names [lsort [array names array $pattern]]
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 returnthis "[format "%-*s = %s" $maxl $nameString $array($name)]\n"
}
return $returnthis
}
set ::tclutilflag true
return "ok"
$$ language pltcl;Next we change testquery1 (save as testquery2) to use the new proc:
create or replace function testquery2(text) returns text as $$
set infostring ""
if {![info exists ::tclutilflag]} {
spi_exec "select tclutil() as temp"
# testing:
append infostring "Functions loaded $temp.\n"
}
spi_exec -array ARR $1 {
append infostring "enter Loop: --------------------\n"
append infostring [rarray ARR]
}
return $infostring
$$ language pltcl;That's all. We test the tclutil flag to see if we have to load the functions, that can be done by one spi_exec.
projectone=# select testquery2('select * from table1');
testquery2
----------------------------------
Functions loaded ok.
enter Loop: --------------------
ARR(.tupno) = 0
ARR(id) = 1
ARR(name) = Peter
enter Loop: ---------------
<snip>On the second run the functions are there already, just check it with eval_me('info var ::*') and eval_me('info proc')
Just add any helper proc you need to tclutil and be sure to test the tclutilflag variable in every function that depends on it.
My favorite test tool can be used here, in the untrusted version, then immediately follows one test:
projectone=# create or replace function eval_meu(text) returns text as $_$
return "[eval $1]"
$_$ language pltclu;
CREATE FUNCTION
projectone=# select eval_meu('exec ls -l /usr/share');
eval_meu
---------------------------------------------------------------------------------------------
total 556
drwxr-xr-x 2 root root 4096 Feb 15 17:11 aclocal
drwxr-xr-x 2 root root 4096 Jul 19 2010 aclocal-1.11
drwxr-xr-x 2 root root 4096 Jul 12 2010 adduser
drwxr-xr-x 6 root root 4096 Jul 12 2010 apache2
drwxr-xr-x 2 root root 4096 Jul 12 2010 applications
drwxr-xr-x 6 root root 4096 Jul 12 2010 apport
<snip>Okay, the whole system is open to SQL now. If this is what you want...
Note: When accessing the OS in pltclu you are user postgres.
We will use hierarchical data in a table, the names are inspired by a menu tree.
After the first tests above you should know how it works, so I only show the code parts.
First create the test data by pasting these statements into psql:
create table table2 (id integer primary key, name text, parent integer); insert into table2 values (1,'View',null),(2,'Insert',null),(3,'Extras',null); insert into table2 values (4,'Zoom',1),(5,'Changes',1),(6,'Select all',1); insert into table2 values (7,'bigger',4),(8,'smaller',4); insert into table2 values (9,'view',5),(10,'record',5),(11,'accept',5); insert into table2 values (12,'picture',2); insert into table2 values (13,'from file',12),(14,'scan',12); insert into table2 values (15,'file select',13); insert into table2 values (16,'choose source',14),(17,'request',14);
Now paste the following proc into your tclutil function:
proc treeup {id ilist} {
set separator " >> "
if {[lsearch $ilist $id] > -1} {
return " --LOOP!"
}
lappend ilist $id
set preparemarker ""
if {![info exists ::PREP(treeup)]} {
set ::PREP(treeup) [spi_prepare "SELECT parent, name from table2 where id = \$1 " [ list integer] ]
# testing:
set preparemarker " !PREPARED! "
}
spi_execp $::PREP(treeup) [list $id] {
if {[info exists parent]} {
return "$preparemarker[treeup $parent $ilist]$separator$name"
} else {
return "$preparemarker$name"
}
}
}Code-walk: The proc gets an index and a list, if this index is found in the list we have a loop (entry already visited).
Now we add the index to the list. We prepare the sql statement if necessary, note the usage of the global variable ::PREP. Then we check if the current entry has a parent, if yes we visit the parent doing a recursive call, else we just return the name of the current entry.
Next we write the function we want to call by sql:
create or replace function treeup(integer) returns text as $_$
# arg 1 ist id
if {![info exists ::tclutilflag]} {
spi_exec "select tclutil() as temp"
}
if {[argisnull 1]} {
return ""
} else {
return [treeup $1 [list ]]
}
$_$ language pltcl;Be sure to "create or replace" the tclutil function after pasting "proc treeup". Now since prepared statements, functions and procs are stored in your session, you should care about restarting psql. Failing to do so will make you test the old code.
You should be aware of the difference between treeup(integer) and proc treeup {id ilist}.
We test the new function:
projectone=# select id, name, treeup(id) as path from table2 order by path; id | name | path ----+---------------+----------------------------------------------- 3 | Extras | Extras 2 | Insert | Insert 12 | picture | Insert >> picture 13 | from file | Insert >> picture >> from file 15 | file select | Insert >> picture >> from file >> file select 14 | scan | Insert >> picture >> scan 16 | choose source | Insert >> picture >> scan >> choose source 17 | request | Insert >> picture >> scan >> request 1 | View | !PREPARED! View 5 | Changes | View >> Changes 11 | accept | View >> Changes >> accept 10 | record | View >> Changes >> record 9 | view | View >> Changes >> view 6 | Select all | View >> Select all 4 | Zoom | View >> Zoom 7 | bigger | View >> Zoom >> bigger 8 | smaller | View >> Zoom >> smaller (17 Zeilen)
Now the sql statement is prepared only once per session, automatically, and reused on every tcl procedure call. The parameter for the where-condition is inserted perfectly.
More tests: create a circular reference in the test data and see if the proc takes care.
There are some sql errors you might expect, like "duplicate key" or violation of a foreign key constraint.
How can pltcl handle it? See yourself - first insert this test data into your database:
create table reftable (id serial primary key, name text not null);
create table dattable (id serial primary key, name text, ref integer references reftable(id) on delete restrict);
insert into reftable (name) values ('attorney'),('programmer');
insert into dattable (name,ref) values ('Peter',2), ('Frank',1);Here is the test function:
create or replace function findsqlstate() returns text as $_$
set res ""
set sql "insert into dattable values (1,'Peter',1);"
#set sql "delete from reftable where id=2"
if {[catch {spi_exec $sql} catchres]} {
append res "CATCH: $catchres\n"
} else {
append res $catchres
}
append res "Errorcode: $::errorCode\nErrorinfo: $::errorInfo"
return $res
$_$ language pltcl;And here the execution:
projectone=# select findsqlstate();
findsqlstate
---------------------------------------------------------------------------
CATCH: duplicate key value violates unique constraint "dattable_pkey"
Errorcode: NONE
Errorinfo: duplicate key value violates unique constraint "dattable_pkey"
while executing
"spi_exec $sql"
(1 Zeile)Result: the variable ::errorCode is not used, the variable ::errorInfo is of limited use because the "catched" value is the same. The only way to deal with the error is a catch statement, if you do not want to propagate the error to the calling program. No sql state codes available here, that is bad, you would need to scan the error message for key words.