'''[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