'''[http://www.postgresql.org/%|%PostgreSQL]''' is an [Open
Source%|%open-source] full [ACID] [SQL] [database] system.  Pronounced
"post-grehs-Q-L", though in the case of MS SQL Server, SQL is pronounced as "sequel".



** See Also **

   [Quick-start guide to use of PostgreSQL with Tcl]:   uses the [Pgtcl] package directly. Use this for very fast, low(-ish) level access.

   [Introduction to Database Access With nstcl]:   details how to use PostgreSQL using [nstcl]. This is a more highlevel interface to Postgresql and other databases (uses [Pgtcl] but that is mostly hidden from view).

   [pgtclsh and pgtksh]:   


** Description **

PostgreSQL is a derivitive of POSTGRES 4.2, converted to ANSI [C].  POSTGRES is
a database management system.  It is compliant with ANSI SQL92 and SQL89.  It
supports a number of enhancements, such as [inheritance], declaritive queries,
optimization, [concurrency concepts%|%concurrency] control, transactions,
multi-user support, user defined operators, types, functions, and access
methods.  APIs exist for C, C++, Java, Perl4, Perl5, Python, SQL and Tcl.  A
[JDBC] driver is also included in the main distribution.  Free drivers for ODBC
are available as a separate download.  A commercial version of PostgreSQL is
available via Illustra, Inc.  The most recent version is 7.4.

PostgreSQL was previously distributed with various language bindings, but
current versions no longer include those.  They are available separately.

Starting with version 8.0, the server side of Postgres is also available for
native [Windows] (NT, 2000, XP, etc. variants only). Previous versions required
[Cygwin] to run the PostgreSQL server. Client access libraries have always been
available on Win32.

PostgreSQL and Tcl have a long history with each other. Tcl bindings such as
[pgtcl] are available for communicating with the server from Tcl scripts, while

One of PG's technical advantages among databases is its asynchronous
notification feature.  Tcl's event loop nicely complements this.


** Resources **

   [http://pgfoundry.org/%|%pgoundry]:   projects server for PostgreSQL


** Utilities **

   [PgBrowse]:   a [MacOS]-oriented client


   [pltcl]:   script triggers and stored procedures in the PostgreSQL server

   [libtclpq] ,by [Colin McCormack]:   is a quick/experimental [Swig]-generated hack with [itcl] wrapper to permit [asynchronous methods%|%asynchronous] execution of [PostgreSQL] commands and cancellation of commands in progress


** Bindings **

   [TDBC]:   this standard package, shipped with Tcl, includes a PostgreSQL driver

   [SQL Relay]:   

   [pgtcl]:   

   [pgtclng]:   

   [pgintcl]:   a pure Tcl interface to, which is itself written  entirely in Tcl and does not rely on libpq.

   [nsdbipg]:   a native Postgresql driver for the easy-to-use [nsdbi] interface: dbi_1row {select a, b, from table where key = :k} (That's all you need to know!)

   [TclODBC]:   this works fine for PostgreSQL.  [Artur Trzewik] 2003-12-11: TclODBC lacks some functions that are possible with Pgtcl. That are: movable cursor (seek command on cursor position) and nested queries in one database handle. I think they are possible in ODBC but not implemented in TclODBC.

   [PQTcl]:   

   [Postgres_pqatcl]:   


pgtcl and pgtcl-ng have binaries available for windows. pgintcl is
platform-independent.


** Convenience Command:  pg **


I found, that the syntax of the Pgtcl commands are not the Tcl way. So I wrote
a little wrapper around the commands provided by the Pgtcl package to make them
more tcl-like:

======
proc pg {cmd args} {
    switch $cmd {
       connect {pg_connect [lindex $args end]}
       disconnect {pg_disconnect [lindex $args end]}
       execute {pg_exec [lindex $args 0] [lindex $args 1]}
       getrow {pg_result [lindex $args 0] -getTuple [lindex $args 1]}
       clear {pg_result [lindex $args 0] -clear}
       status {pg_result [lindex $args 0] -status}
       rowcount {pg_result [lindex $args 0] -numTuples}
    }
}
======

This adds a command pg to Tcl which I like far more than the original one. Instead of saying

======
pg_connect "mydb"
pg_disconnect $myHandle
pg_result $myResult -clear
======

I just type

======
pg connect "mydb"
pg disconnect $myHandle
pg clear $myResult
======

Please note, that there is no error handling and checking in the command pg and
you can easily type in wrong things. It would be best to put this command in
it's own namespace and provide for error checking etc. You could even add more
funcionality like a loop command for query results like in nstcl


** pg_execute Performance Trick **

MB 2003-11-13:

I got caught out by a problem using pg_execute.
pg_execute evaluates the supplied script for each row of the result. I was appending each result to a list.
The problem was that the speed scaled very poorly as the number of records increased.
I boiled it down to this test case:

======
set a {} 
time [list pg_execute pgsql5 {SELECT * FROM journal LIMIT 1000} {
append a {23456789012345678901234567890123456789012345678901234567890}}]
======

It turned out to be because append was, of course, returning the whole variable, which was quite big.
pg_execute was internally doing something slow with what was returned. Changing the test case to:

======
set a {} 
time [list pg_execute pgsql5 {SELECT * FROM journal LIMIT 1000} {
append a {23456789012345678901234567890123456789012345678901234567890} ; list}]
======

fixed the problem. This reduced the time to read 10,000 records from about half a minute to 170 milliseconds!



<<categories>> PostgreSQL | Database