Pgtcl is the package name of the PostgreSQL database driver libpgtcl.
package require Pgtcl # Obsolete: load ./libpgtcl.so
Pgtcl 1.5 release (ZB 20110909 Actual version seems to be V1.7)
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