Updated 2011-09-09 12:09:45 by ZB ▲

Pgtcl is the package name of the PostgreSQL database driver libpgtcl.
  package require Pgtcl

  # Obsolete:
  load ./libpgtcl.so

Pgtcl 1.5 release (ZB 20110909 V1.7 seems to be actual version)

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

Category PostgreSQL Category Database Category Package