Pgtcl is the name of the Postgres Tcl package that is made available when libpgtcl is built and installed.
In the past, many people have loaded libpgtcl by using an explicit load...
load ./libpgtcl.so.14
In the modern era, the libpgtcl shared library is built and installed and automatically loaded when the Pgtcl package is requested...
package require Pgtcl
Pgtcl 1.5 release (soon to be released)
With version 1.5, there are some new features added, that I will demonstrate by annotating an interactive tclsh session:
#
# load a 8.5 interp...has dictionary support
#
schwarz@thor:~/code/cvs/libpgtcl> /usr/local/tcl8.5/bin/tclsh8.5
% load ./libpgtcl1.5.so
% array set conninfo {
host myhost
port 5000
dbname template1
user postgres
}
#
# 2 things to notice: (1) there is Tcl namespace support (2) there is a new option to pg_connect
# called -connlist, which takes a name/value Tcl list as it's value
#
% set conn(1) [pg::connect -connlist [array get conninfo]]
pgsql3
#
# There is a new option to pg_connect, where you can specify the connection handle name to use
#
% set conn(2) [pg::connect -connlist [array get conninfo] -connhandle myhan]
myhan
#
# Using the -connhandle option won't clobber an existing connection
#
% set conn(3) [pg::connect -connlist [array get conninfo] -connhandle pgsql3]
Connection to database failed
handle already exists
#
# can import the pg commands
#
% namespace import ::pg::*
#
# new command, pg_dbinfo which returns the connection handles or result handles
#
% dbinfo connections
myhan pgsql3
#
# You can now use the connection/result handle as a command
#
% set res(1) [$conn(1) exec "SELECT relname FROM Pg_class LIMIT 5"]
pgsql3.0
% set res(2) [$conn(2) exec {SELECT * FROM Pg_class LIMIT $1} 4]
myhan.0
% dbinfo results $conn(1)
pgsql3.0
% dbinfo results $conn(2)
myhan.0
% $res(1) -list
views data_type_privileges element_types pg_toast_17173_index pg_toast_17173
% $res(2) -llist
{views 17057 17219 1 0 17218 0 0 0 0 0 f f v 7 0 0 0 0 0 f f t f {{postgres=arwdRxt/postgres,=r/postgres}}}
{data_type_privileges 17057 17222 1 0 17221 0 0 0 0 0 f f v 5 0 0 0 0 0 f f t f {{postgres=arwdRxt/postgres,=r/postgres}}}
{element_types 17057 17226 1 0 17225 0 0 0 0 0 f f v 29 0 0 0 0 0 f f t f {{postgres=arwdRxt/postgres,=r/postgres}}}
{pg_toast_17173_index 99 0 1 403 17177 0 1 0 0 0 f f i 2 0 0 0 0 0 f f f f {}}
#
# there is a -dict option to pg_result, which returns the results as a dict object. NOTE that this is
# experimental, since 8.5 is not final, and the API could change
#
% set mydict [$res(2) -dict]
3 {relpages 1 relfkeys 0 relnatts 2 relhasoids f relhassubclass f relam 403 reltoastidxid 0 reltuples 0 relacl {}
relhaspkey f reltriggers 0 relname pg_toast_17173_index relrefs 0 reltype 0 relukeys 0 relchecks 0 relisshared f
reltablespace 0 reltoastrelid 0 relnamespace 99 relowner 1 relfilenode 17177 relkind i relhasrules f relhasindex f} 2
{relpages 0 relfkeys 0 relnatts 29 relhasoids f relhassubclass f relam 0 reltoastidxid 0 reltuples 0 relacl
{{postgres=arwdRxt/postgres,=r/postgres}} relhaspkey f reltriggers 0 relname element_types relrefs 0 reltype 17226 relukeys
0 relchecks 0 relisshared f reltablespace 0 reltoastrelid 0 relnamespace 17057 relowner 1 relfilenode 17225 relkind v
relhasrules t relhasindex f} 1 {relpages 0 relfkeys 0 relnatts 5 relhasoids f relhassubclass f relam 0 reltoastidxid 0
reltuples 0 relacl {{postgres=arwdRxt/postgres,=r/postgres}} relhaspkey f reltriggers 0 relname data_type_privileges
relrefs 0 reltype 17222 relukeys 0 relchecks 0 relisshared f reltablespace 0 reltoastrelid 0 relnamespace 17057 relowner 1
relfilenode 17221 relkind v relhasrules t relhasindex f} 0 {relpages 0 relfkeys 0 relnatts 7 relhasoids f relhassubclass f
relam 0 reltoastidxid 0 reltuples 0 relacl {{postgres=arwdRxt/postgres,=r/postgres}} relhaspkey f reltriggers 0 relname
views relrefs 0 reltype 17219 relukeys 0 relchecks 0 relisshared f reltablespace 0 reltoastrelid 0 relnamespace 17057
relowner 1 relfilenode 17218 relkind v relhasrules t relhasindex f}
% dict get $mydict 1
relpages 0 relfkeys 0 relnatts 5 relhasoids f relhassubclass f relam 0 reltoastidxid 0 reltuples 0 relacl
{{postgres=arwdRxt/postgres,=r/postgres}} relhaspkey f reltriggers 0 relname data_type_privileges relrefs 0 reltype 17222
relukeys 0 relchecks 0 relisshared f reltablespace 0 reltoastrelid 0 relnamespace 17057 relowner 1 relfilenode 17221
relkind v relhasrules t relhasindex f
% dict get $mydict 1 relname
data_type_privileges
#
# renaming a connection/result handle command, has the same effect of eithering pg_disconnect if the
# command is a connection handle, or pg_result $conn -clear, if the command is a result handle.
#
% rename $res(2) {}
% dbinfo results $conn(2)
% dbinfo connections
myhan pgsql3
% dbinfo results pgsql3
pgsql3.0
% foreach C [dbinfo connections] {$C disconnect}
% dbinfo connections
%BAS One thing to note about pgtcl, is that the library forces the PGCLIENTENCODING to UNICODE. As long as there is a character set conversion between the server encoding and unicode, you should be ok. See here for the conversion table: http://www.postgresql.org/docs/8.1/interactive/multibyte.html#AEN22506
RS 2006-12-08: Here's how to introspect the tables of a connected PostgreSQL database (handle in db):
proc get_tables db {
set tables {}
pg_select $db {SELECT relname FROM pg_class WHERE
relname !~ '^pg_' and relkind='r' and relname !~ '^sql_'
} ar {lappend tables $ar(relname)}
set tables
}BAS Also note that there are several helper procs that come with pgtcl. For example, look under playpen/pggrok/ for introspection procs