pgtcl is a tcl package for front-end programs to interface with Postgres backends. It makes most of the functionality of libpq available to tcl scripts. This package was originally written by Jolly Chen. Command Description pg_connect opens a connection to the backend server pg_disconnect closes a connection pg_conndefaults get connection options and their defaults pg_exec send a query to the backend pg_result manipulate the results of a query pg_select loop over the result of a SELECT statement pg_listen establish a callback for NOTIFY messages 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 Unix file into a large object pg_lo_export export a large object into a Unix file These commands are described further on subsequent pages. The pg_lo* routines are interfaces to the Large Object features of Postgres. 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/END 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/END transaction block. Here's a small example of how to use the routines: # 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 } pg_connect PGTCL - Connection Management pg_connect opens a connection to the backend server pgtclconnecting pg_connect 1997-12-24 pg_connect -conninfo connectOptions pg_connect dbName -host hostName -port portNumber -tty pqtty -options optionalBackendArgs 1998-10-07 connectOptions A string of connection options, each written in the form keyword = value. 1997-12-24 dbName Specifies a valid database name. -host hostName Specifies the domain name of the backend server for dbName. -port portNumber Specifies the IP port number of the backend server for dbName. -tty pqtty Specifies file or tty for optional debug output from backend. -options optionalBackendArgs Specifies options for the backend server for dbName. 1997-12-24 dbHandle If successful, a handle for a database connection is returned. Handles start with the prefix "pgsql". 1997-12-24 pg_connect opens a connection to the Postgres backend. Two syntaxes are available. In the older one, each possible option has a separate option switch in the pg_connect statement. In the newer form, a single option string is supplied that can contain multiple option values. See pg_conndefaults for info about the available options in the newer syntax. XXX thomas 1997-12-24 pg_disconnect PGTCL - Connection Management pg_disconnect closes a connection to the backend server pgtclconnecting pg_connect 1997-12-24 pg_disconnect dbHandle 1997-12-24 dbHandle Specifies a valid database handle. 1997-12-24 None 1997-12-24 pg_disconnect closes a connection to the Postgres backend. pg_conndefaults PGTCL - Connection Management pg_conndefaults obtain information about default connection parameters pgtclconnecting pg_conndefaults 1998-10-07 pg_conndefaults 1998-10-07 None. 1998-10-07 option list The result is a list describing the possible connection options and their current default values. Each entry in the list is a sublist of the format: {optname label dispchar dispsize value} where the optname is usable as an option in pg_connect -conninfo. 1998-10-07 pg_conndefaults returns info about the connection options available in pg_connect -conninfo and the current default value for each option. pg_conndefaults pg_exec PGTCL - Query Processing pg_exec send a query string to the backend pgtclconnecting pg_connect 1997-12-24 pg_exec dbHandle queryString 1997-12-24 dbHandle Specifies a valid database handle. queryString Specifies a valid SQL query. 1997-12-24 resultHandle A Tcl error will be returned if Pgtcl was unable to obtain a backend response. Otherwise, a query result object is created and a handle for it is returned. This handle can be passed to pg_result to obtain the results of the query. 1997-12-24 pg_exec submits a query to the Postgres backend and returns a result. Query result handles start with the connection handle and add a period and a result number. Note that lack of a Tcl error is not proof that the query succeeded! An error message returned by the backend will be processed as a query result with failure status, not by generating a Tcl error in pg_exec. pg_result PGTCL - Query Processing pg_result get information about a query result pgtclconnecting pg_connect 1997-12-24 pg_result resultHandle resultOption 1997-12-24 resultHandle The handle for a query result. resultOption Specifies one of several possible options. -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 the command was an INSERT, the OID of the inserted tuple; otherwise an empty string. -numTuples the number of tuples returned by the query. -numAttrs the number of attributes in each tuple. -list VarName assign the results to a list of lists. -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 attribute's value and the remaining attributes' names as keys. If appendstr is given then it is appended to each key. In short, all but the first field of each tuple are stored into the array, using subscripts of the form (firstFieldValue,fieldNameAppendStr). -getTuple tupleNumber returns the fields of the indicated tuple in a list. Tuple numbers start at zero. -tupleArray tupleNumber arrayName stores the fields of the tuple in array arrayName, indexed by field names. Tuple numbers start at zero. -attributes returns a list of the names of the tuple attributes. -lAttributes returns a list of sublists, {name ftype fsize} for each tuple attribute. -clear clear the result query object. 1997-12-24 The result depends on the selected option, as described above. 1997-12-24 pg_result returns information about a query result created by a prior pg_exec. You can keep a query result around for as long as you need it, but when you are done with it, be sure to free it by executing pg_result -clear. Otherwise, you have a memory leak, and Pgtcl will eventually start complaining that you've created too many query result objects. pg_select PGTCL - Query Processing pg_select loop over the result of a SELECT statement pgtclconnecting pg_connect 1997-12-24 pg_select dbHandle queryString arrayVar queryProcedure 1997-12-24 dbHandle Specifies a valid database handle. queryString Specifies a valid SQL select query. arrayVar Array variable for tuples returned. queryProcedure Procedure run on each tuple found. 1997-12-24 resultHandle the return result is either an error message or a handle for a query result. 1997-12-24 pg_select submits a SELECT query to the Postgres backend, and executes a given chunk of code for each tuple in the result. The queryString must be a SELECT statement. Anything else returns an error. The arrayVar variable is an array name used in the loop. For each tuple, arrayVar is filled in with the tuple field values, using the field names as the array indexes. Then the queryProcedure is executed. This would work if table "table" has fields "control" and "name" (and, perhaps, other fields): pg_select $pgconn "SELECT * from table" array { puts [format "%5d %s" array(control) array(name)] } pg_listen PGTCL - Asynchronous Notify pg_listen sets or changes a callback for asynchronous NOTIFY messages pgtclnotify notify 1998-5-22 pg_listen dbHandle notifyName callbackCommand 1998-5-22 dbHandle Specifies a valid database handle. notifyName Specifies the notify condition name to start or stop listening to. callbackCommand If present and not empty, provides the command string to execute when a matching notification arrives. 1998-5-22 None 1998-5-22 pg_listen creates, changes, or cancels a request to listen for asynchronous NOTIFY messages from the Postgres backend. With a callbackCommand parameter, the request is established, or the command string of an already existing request is replaced. With no callbackCommand parameter, a prior request is canceled. After a pg_listen request is established, the specified command string is executed whenever a NOTIFY message bearing the given name arrives from the backend. This occurs when any Postgres client application issues a NOTIFY command referencing that name. (Note that the name can be, but does not have to be, that of an existing relation in the database.) The command string is executed from the Tcl idle loop. That is the normal idle state of an application written with Tk. In non-Tk Tcl shells, you can execute update or vwait to cause the idle loop to be entered. You should not invoke the SQL statements LISTEN or UNLISTEN directly when using pg_listen. Pgtcl takes care of issuing those statements for you. But if you want to send a NOTIFY message yourself, invoke the SQL NOTIFY statement using pg_exec. pg_lo_creat PGTCL - Large Objects pg_lo_creat create a large object pgtclcreating pg_lo_creat 1997-12-24 pg_lo_creat conn mode 1997-12-24 conn Specifies a valid database connection. mode Specifies the access mode for the large object 1997-12-24 objOid The oid of the large object created. 1997-12-24 pg_lo_creat creates an Inversion Large Object. mode can be any OR'ing together of INV_READ, INV_WRITE, and INV_ARCHIVE. The OR delimiter character is "|". [pg_lo_creat $conn "INV_READ|INV_WRITE"] pg_lo_open PGTCL - Large Objects pg_lo_open open a large object pgtclopening pg_lo_open 1997-12-24 pg_lo_open conn objOid mode 1997-12-24 conn Specifies a valid database connection. objOid Specifies a valid large object oid. mode Specifies the access mode for the large object 1997-12-24 fd A file descriptor for use in later pg_lo* routines. 1997-12-24 pg_lo_open open an Inversion Large Object. Mode can be either "r", "w", or "rw". pg_lo_close PGTCL - Large Objects pg_lo_close close a large object pgtclclosing pg_lo_close 1997-12-24 pg_lo_close conn fd 1997-12-24 conn Specifies a valid database connection. fd A file descriptor for use in later pg_lo* routines. 1997-12-24 None 1997-12-24 pg_lo_close closes an Inversion Large Object. pg_lo_read PGTCL - Large Objects pg_lo_read read a large object pgtclreading pg_lo_read 1997-12-24 pg_lo_read conn fd bufVar len 1997-12-24 conn Specifies a valid database connection. fd File descriptor for the large object from pg_lo_open. bufVar Specifies a valid buffer variable to contain the large object segment. len Specifies the maximum allowable size of the large object segment. 1997-12-24 None 1997-12-24 pg_lo_read reads at most len bytes from a large object into a variable named bufVar. bufVar must be a valid variable name. pg_lo_write PGTCL - Large Objects pg_lo_write write a large object pgtclwriting pg_lo_write 1997-12-24 pg_lo_write conn fd buf len 1997-12-24 conn Specifies a valid database connection. fd File descriptor for the large object from pg_lo_open. buf Specifies a valid string variable to write to the large object. len Specifies the maximum size of the string to write. 1997-12-24 None 1997-12-24 pg_lo_write writes at most len bytes to a large object from a variable buf. buf must be the actual string to write, not a variable name. pg_lo_lseek PGTCL - Large Objects pg_lo_lseek seek to a position in a large object pgtclpositioning pg_lo_lseek 1997-12-24 pg_lo_lseek conn fd offset whence 1997-12-24 conn Specifies a valid database connection. fd File descriptor for the large object from pg_lo_open. offset Specifies a zero-based offset in bytes. whence whence can be "SEEK_CUR", "SEEK_END", or "SEEK_SET" 1997-12-24 None 1997-12-24 pg_lo_lseek positions to offset bytes from the beginning of the large object. whence can be "SEEK_CUR", "SEEK_END", or "SEEK_SET". pg_lo_tell PGTCL - Large Objects pg_lo_tell return the current seek position of a large object pgtclpositioning pg_lo_tell 1997-12-24 pg_lo_tell conn fd 1997-12-24 conn Specifies a valid database connection. fd File descriptor for the large object from pg_lo_open. 1997-12-24 offset A zero-based offset in bytes suitable for input to pg_lo_lseek. 1997-12-24 pg_lo_tell returns the current to offset in bytes from the beginning of the large object. pg_lo_unlink PGTCL - Large Objects pg_lo_unlink delete a large object pgtcldelete pg_lo_unlink 1997-12-24 pg_lo_unlink conn lobjId 1997-12-24 conn Specifies a valid database connection. lobjId Identifier for a large object. XXX Is this the same as objOid in other calls?? - thomas 1998-01-11 1997-12-24 None 1997-12-24 pg_lo_unlink deletes the specified large object. pg_lo_import PGTCL - Large Objects pg_lo_import import a large object from a Unix file pgtclimport pg_lo_import 1997-12-24 pg_lo_import conn filename 1997-12-24 conn Specifies a valid database connection. filename Unix file name. 1997-12-24 None XXX Does this return a lobjId? Is that the same as the objOid in other calls? thomas - 1998-01-11 1997-12-24 pg_lo_import reads the specified file and places the contents into a large object. pg_lo_import must be called within a BEGIN/END transaction block. pg_lo_export PGTCL - Large Objects pg_lo_export export a large object to a Unix file pgtclexport pg_lo_export 1997-12-24 pg_lo_export conn lobjId filename 1997-12-24 conn Specifies a valid database connection. lobjId Large object identifier. XXX Is this the same as the objOid in other calls?? thomas - 1998-01-11 filename Unix file name. 1997-12-24 None XXX Does this return a lobjId? Is that the same as the objOid in other calls? thomas - 1998-01-11 1997-12-24 pg_lo_export writes the specified large object into a Unix file. pg_lo_export must be called within a BEGIN/END transaction block.