'''[http://flightaware.github.io/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/archive/master.zip%|%master.zip]:   


** 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
[http://www.postgresql.org/docs/9.2/interactive/multibyte.html%|%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 **

======none
# 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
%
======

** 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


<<categories>> PostgreSQL | Database | Application | Package