pgtcl is a tcl package for front-end programs to interface with Postgres backends. pgtcl does not use the libpq library but communicates to the backend directly via the frontend-backend protocol. Thus, it is more efficient than previous postgres->tcl bindings which are layered on top of libpq. In addition, pgtcl can handle multiple backend connections from a single frontend application. 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_exec send a query to the backend pg_select loop over the result of a select statement pg_result manipulate the results of a query 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 Some commands equivalent to libpq commands are provided for connection and query operations. 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 "pgp" 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. Handles start with the prefix "pgp". 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 -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.