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:
I would like to add some information that is missing from the Postgresql documentation. So we start with a working postgres server, are logged 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.
See Also: