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. The pg_lo* routines are interfaces to the Inversion Large Objects in Postgres. The functions are designed to mimic the analogous file system functions in the standard Unix file system interface. 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 on 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 should typically 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_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 dbName -host hostName -port portNumber -tty pqtty -options optionalBackendArgs 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 (need information thomas 1997-12-24) -options optionalBackendArgs Specifies options for the backend server for dbName. 1997-12-24 dbHandle The return result is either an error message or a handle for a database connection. Handles start with the prefix "pgsql" 1997-12-24 pg_connect opens a connection to the Postgres backend. 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_exec PGTCL - Connection Management 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 queryHandle the return result is either an error message or a handle for a query result. 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. 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 notification 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. pg_select PGTCL - Connection Management 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 queryHandle the return result is either an error message or a handle for a query result. 1997-12-24 pg_select submits a query to the Postgres backend. and returns the results. The queryString must be a select statement. Anything else returns an error. The arrayVar variable is an array name used in the loop. It is filled out with the result of the query for each tuple using the field names as the associative indices. set DB "mydb" set conn [pg_connect $DB] pg_select $conn "SELECT * from table" array { puts [format "%5d %s" array(control) array(name)] } pg_disconnect $conn pg_result PGTCL - Connection Management pg_result get information about a query result pgtclconnecting pg_connect 1997-12-24 pg_result queryHandle resultOption 1997-12-24 queryHandle The handle for a query result. resultOption Specifies one of several possible options. -status the status of the result. -oid if the last query was an insert, returns the oid of the inserted tuple -conn the connection that produced the result -assign arrayName assign the results to an array -assignbyidx arrayName ?appendstr? assign the results to an array using the first field as a key and optionally append appendstr to the key name. Useful for creating pseudo-multi dimensional arrays in tcl. -numTuples the number of tuples in the query -attributes returns a list of the name/type pairs of the tuple attributes -getTuple tupleNumber returns the values of the tuple in a list -clear clear the result buffer. Do not reuse after this 1997-12-24 queryHandle the return result is either an error message or a handle for a query result. 1997-12-24 pg_result returns information about a query. 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 on 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.