Pgtcl

Pgtcl, aka libpgtcl, is a Tcl package for client programs to interface with PostgreSQL servers. It makes most of the functionality of libpq available to Tcl scripts. Developed for over ten years and originally bundled within pre-8.0 releases of PostgreSQL, Pgtcl is now a standalone project for easier cross-version support and maintainability.

Attributes

website
http://flightaware.github.io/Pgtcl/

Download

   https://github.com/flightaware/Pgtcl/releases

Documentation

Quick-start guide to use of PostgreSQL with Tcl

Description

Pgtcl is a C interface library that glues Tcl to PostgreSQL. Technically, Pgtcl is the Tcl package that contains the libpgtcl library. It links with PostgreSQL's libpq library to provide a thorough interface with PostgreSQL's capabilities. libpgtcl is TEA 3.1 compliant. It has a standard GNU configure script, and is known to work on many variants of Linux, FreeBSD, Mac OS X, and even Windows.

The configure script does a pretty good job of figuring out where the Tcl and PostgreSQL includes and libraries are. It looks for a tclConfig.sh (and can be told where to look, see the README) and find the Postgres stuff it needs via pg_config.

Is libpgtcl thread safe or no? I don't know

Older versions of Pgtcl do not work well with newer (8.5+) Tcl versions, eg. data retrieval into an array.

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 the conversion table.

Usage

package require Pgtcl

# Obsolete:
load ./libpgtcl.so

Basic Example

# connect to database, execute select query, retrieve data, print and quit
package require Pgtcl
set db [pg_connect -conninfo "dbname=postgres user=someuser password=somepassword"]
set result [pg_exec $db "select * from sample_table;"]
pg_result $result -assign some_array
parray some_array
pg_result $result -clear
pg_disconnect $db

Feature Example

# load a 8.5 interp...has dictionary support
#
[email protected]:~/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
%

Introspecting Tables

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