Updated 2017-09-13 14:54:19 by HolgerJ ▲

PostgreSQL and Tcl combine quite pleasantly. It can be a delight using them for substantial development projects. Much the more demanding, at least in setup, is the former. Here's what it takes to start: ...

It may be worth having a look at TDBC[1], which is a database neutral way of connecting a database with Tcl.

Getting Started edit

1. Setup PostgreSQL

Either just install your OS specific package. For Linux it is available in most distributions. For almost all platforms, there are prepackaged versions. If all else fails start reading the fine PostgreSQL install instructions at:

https://www.postgresql.org/docs/current/static/installation.html

Make sure you have a running server, get its connection info like hostname, port, user and password for the user you want to use for the next steps.

2. Make sure you have a working Pgtcl package

Try:
package require Pgtcl

If it works, you have a probably working Pgtcl package installed, otherwise you need to install/compile one from either the PostgreSQL distribution you have installed (Pgtcl should be included there) or obtain and build Pgtcl.

Googie 09/09/2011 - There's also a pgintcl, a pure-Tcl package with very similar (yet slightly different) API.

3. Start using Pgtcl

Documentation

  • Online HTML documentation for more recent versions of PostgreSQL unfortunately does not cover pgtcl.
  • Documentation can be downloaded from http://flightaware.github.com/Pgtcl/
  • HTML documentation is included with pgtcl packages for Linux distributions (e.g. Red Hat's postgresql-tcl RPM)

The basics:
 # select and do something with the results...
 # open connection
 # password etc. must be provided according to the security settings on the postgres db
 # you may be able to just do "pg_connect www" or even "pg_connect" if database is your username

 package require Pgtcl

 set db [pg_connect -conninfo [list host = localhost user = test dbname = testdb]]

 pg_select $db "select * from testtable" user {
     parray user
 }

 pg_disconnect $db

Inserting into a database...

Note how pg_exec returns a result handle that has many things that can be done with it to access the results, which is done using pg_result. You can get a list interactively by doing a pg_result with no arguments. You gotta clean up the result handle this way. Note that pg_select, above, takes care of all that for you.
 package require Pgtcl

 proc doit {} {
     set fp [open sampledata.txt]
     set conn [pg_connect -conninfo ""]

     while {[gets $fp line] >= 0} {
         lassign $line name address city state zip

         set statement "insert into peopletable values ( \
            [pg_quote $name], [pg_quote $address], [pg_quote $city] \
            [pg_quote $state], [pg_quote $zip]);"

        set result [pg_exec $conn $statement]
        if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
            puts "[pg_result $result -error] executing '$statement'"
        }
        pg_result $result -clear
    }
 }

 if !$tcl_interactive doit

Recent version of Pgtcl running with fairly recent version of PostgreSQL can do variable substitutions, which are pretty cool, and require less quoting and stuff. Observe...
        set statement {insert into peopletable values ($1, $2, $3, $4, $5);}

        set result [pg_exec $conn $statement $name $address $city $state $zip]

You can also prepare statements, which is a way to improve performance of a statement that is going to be frequently executed, by getting PostgreSQL to sort of preparse it and figure it out once rather than every time it is presented.
 package require Pgtcl

 proc doit {} {
    set fp [open sampledata.txt]
    set conn [pg_connect -conninfo ""]

    set result [pg_exec $conn {prepare insert_peopletable \
        (varchar, varchar, varchar, varchar, varchar, varchar) as \
         insert into peopletable values ($1, $2, $3, $4, $5);}]

    if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
        puts "[pg_result $result -error] preparing statement"
        exit 1
    }

    while {[gets $fp line] >= 0} {
         lassign $line name address city state zip
        set result [pg_exec_prepared $conn pgtest_insert_people \
            $name $address $city $state $zip]

        if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
            puts "[pg_result $result -error] inserting '$line'"
        }
        pg_result $result -clear
    }
 }

 if !$tcl_interactive doit

4. Pgtcl Commands

pg_quote $string

This escapes a string by making it Postgres-safe. It quotes single quotes and backslashes. If you're doing something like
 pg_exec "insert into foo(name='$name');"

...and name contains a single quote, it'll fail. Worse, it makes you vulnerable to SQL injection attacks.

Please run value strings through pg_quote to make sure they can be used as values and stuff in Postgres.
 pg_exec "insert into foo(name=[pg_quote $name]);"

Any special characters that occur in name, such as single quote or backslash, will be properly quoted.
 set conn [pg_connect -conninfo conninfoString]

The modern way to connect, all necessary information required for the connection (host, port, database, user, password, etc.) is specified in the connect info string. You usually don't have to pass everything, often only the database name.
 pg_disconnect $conn

Close a backend connection.
 set res [pg_exec $conn query var...]

Send a query string to the backend connection.

The return result is either an error or a handle for a query result. Handles start with the prefix "pgp". On recent versions of Pgtcl and PostgreSQL, vars can be specified and substituted for dollar-number variables in the query. See the example earlier in the page.

pg_select

Send a select query string to the backend connection.
         pg_select connection query arrayName code

The query must be a select statement. The array named arrayName will be created as local variable, and be visible only inside the code. It will contain four "special" elements whose name starts with a ".", plus one element for each column. E.g.:
 result(.command)    = update
 result(.headers)    = error_class comment
 result(.numcols)    = 2
 result(.tupno)      = 58
 result(comment)     =
 result(error_class) = PLANETCHECK

The problem with this kind of statement is that if you have several columns with the same name, only one of them is correctly returned. This can happen easily when you join several tables sharing a column name which is not used as join criterion. All columns are retained, but the values of all but one of them are lost.

.numcols should be the list length of .headers, RS assumes.

The code is run once for each row found. See the example earlier in this page.

You can continue and return in the body and it'll do the expected thing. (What I think is expected, anyway.)

pg_result

Get information about the results of a query.
    pg_result $res ?option?

The options are:
        -status         the status of the result

        -error          the error message, if the status indicates error;
                             otherwise an empty string

        -conn           the connection that produced the result

        -oid            if command was an INSERT, the OID of the inserted tuple

        -numTuples      the number of tuples in the query

        -numAttrs       returns the number of attributes returned by the query

        -assign arrayName
                        assign the results to an array, using
                            subscripts of the form (tupno,attributeName)

        -assignbyidx arrayName ?appendstr?
                        assign the results to an array using the first
                            field's value as a key.

                        All but the first field of each tuple are stored,
                            using subscripts of the form
                            (field0value,attributeNameappendstr)

        -list
                        returns the results as a list

        -llist
                        returns the results as a list of lists, where each list element is the row data

        -getTuple tupleNumber
                        returns the values of the tuple in a list

        -tupleArray tupleNumber arrayName
                        stores the values of the tuple in array arrayName,
                            indexed by the attributes returned

        -attributes     returns a list of the name/type pairs of the tuple
                            attributes

        -lAttributes    returns a list of the {name type len} entries of the
                            tuple attributes

        -clear          clear the result buffer. Do not reuse after this

see getting data from pgtcl for examples on the above options

pg_execute

Send a query string to the backend connection and process the result.
       pg_execute ?-array name? ?-oid varname? connection query ?loop_body?

The return result is the number of tuples processed. If the query returns tuples (i.e. a SELECT statement), the result is placed into variables.

pg_conninfo

Apparently returns a list of all the database connections opened by pg_connect. BAS Yes, it does that as well as return the result handles per connection.
       pg_conninfo connections  - returns current opened connection handles
       pg_conninfo results $con - returns the current opened result handles for $con connection

Category PostgreSQLCategory DatabaseCategory Tutorial