Chapter 3. pgtcl - Tcl Binding Library

Table of Contents
3.1. Introduction
3.2. Loading pgtcl into your application
3.3. pgtcl Command Reference Information
pg_connect -- open a connection to the backend server
pg_disconnect -- close a connection to the backend server
pg_conndefaults -- obtain information about default connection parameters
pg_exec -- send a command string to the server
pg_result -- get information about a query result
pg_select -- loop over the result of a SELECT statement
pg_execute -- send a query and optionally loop over the results
pg_listen -- set or change a callback for asynchronous NOTIFY messages
pg_on_connection_loss -- set or change a callback for unexpected connection loss
pg_lo_creat -- create a large object
pg_lo_open -- open a large object
pg_lo_close -- close a large object
pg_lo_read -- read a large object
pg_lo_write -- write a large object
pg_lo_lseek -- seek to a position in a large object
pg_lo_tell -- return the current seek position of a large object
pg_lo_unlink -- delete a large object
pg_lo_import -- import a large object from a file
pg_lo_export -- export a large object to a file

3.1. Introduction

pgtcl is a Tcl package for client programs to interface with PostgreSQL servers. It makes most of the functionality of libpq available to Tcl scripts.

This package was originally written by Jolly Chen.

Table 3-1 gives an overview over the commands available in pgtcl. These commands are described further on subsequent pages.

Table 3-1. pgtcl Commands

CommandDescription
pg_connectopens a connection to the backend server
pg_disconnectcloses a connection
pg_conndefaultsget connection options and their defaults
pg_execsend a query to the backend
pg_resultmanipulate the results of a query
pg_selectloop over the result of a SELECT statement
pg_executesend a query and optionally loop over the results
pg_listenestablish a callback for NOTIFY messages
pg_on_connection_lossestablish a callback for unexpected connection loss
pg_lo_creatcreate a large object
pg_lo_openopen a large object
pg_lo_closeclose a large object
pg_lo_readread a large object
pg_lo_writewrite a large object
pg_lo_lseekseek to a position in a large object
pg_lo_tellreturn the current seek position of a large object
pg_lo_unlinkdelete a large object
pg_lo_importimport a Unix file into a large object
pg_lo_exportexport a large object into a Unix file

The pg_lo_* routines are interfaces to the large object features of PostgreSQL. The functions are designed to mimic the analogous file system functions in the standard Unix file system interface. The pg_lo_* routines should be used within a BEGIN/COMMIT transaction block because the file descriptor returned by pg_lo_open is only valid for the current transaction. pg_lo_import and pg_lo_export must be used in a BEGIN/COMMIT transaction block.

Example 3-1 shows a small example of how to use the routines.

Example 3-1. pgtcl Example Program

# getDBs :
#   get the names of all the databases at a given host and port number
#   with the defaults being the localhost and port 5432
#   return them in alphabetical order
proc getDBs { {host "localhost"} {port "5432"} } {
    # datnames is the list to be result
    set conn [pg_connect template1 -host $host -port $port]
    set res [pg_exec $conn "SELECT datname FROM pg_database ORDER BY datname"]
    set ntups [pg_result $res -numTuples]
    for {set i 0} {$i < $ntups} {incr i} {
	lappend datnames [pg_result $res -getTuple $i]
    }
    pg_result $res -clear
    pg_disconnect $conn
    return $datnames
}