libpgtcl Tcl 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. gives an overview over the commands available in pgtcl. These commands are described further on subsequent pages. 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_execute send a query and optionally loop over the results pg_listen establish a callback for NOTIFY messages pg_on_connection_loss establish 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 Unix file into a large object pg_lo_export export 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. shows 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 } Before using pgtcl commands, you must load libpgtcl into your Tcl application. This is normally done with the Tcl load command. Here is an example: load libpgtcl[info sharedlibextension] The use of info sharedlibextension is recommended in preference to hard-wiring .so or .sl into the program. The load command will fail unless the system's dynamic loader knows where to look for the libpgtcl shared library file. You may need to work with ldconfig, or set the environment variable LD_LIBRARY_PATH, or use some equivalent facility for your platform to make it work. Refer to the PostgreSQL installation instructions for more information. libpgtcl in turn depends on libpq, so the dynamic loader must also be able to find the libpq shared library. In practice this is seldom an issue, since both of these shared libraries are normally stored in the same directory, but it can be a stumbling block in some configurations. If you use a custom executable for your application, you might choose to statically bind libpgtcl into the executable and thereby avoid the load command and the potential problems of dynamic linking. See the source code for pgtclsh for an example. pg_connect PGTCL - Connection Management pg_connect open 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. A list of valid options can be found in libpq's PQconnectdb() manual entry. 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 PostgreSQL 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 close 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 PostgreSQL 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 command string to the server 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 PostgreSQL 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 0. -numTuples the number of tuples returned by the query. -numAttrs the number of attributes in each tuple. -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 None. 1997-12-24 pg_select submits a SELECT query to the PostgreSQL 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. In addition to the field values, the following special entries are made in the array: .headers A list of the column names returned by the SELECT. .numcols The number of columns returned by the SELECT. .tupno The current tuple number, starting at zero and incrementing for each iteration of the loop body. 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_execute PGTCL - Query Processing pg_execute send a query and optionally loop over the results pgtclquery pg_execute 2002-03-06 pg_execute -array arrayVar -oid oidVar dbHandle queryString queryProcedure 2002-03-06 -array arrayVar Specifies the name of an array variable where result tuples are stored, indexed by the field names. This is ignored if queryString is not a SELECT statement. For SELECT statements, if this option is not used, result tuples values are stored in individual variables named according to the field names in the result. -oid oidVar Specifies the name of a variable into which the OID from an INSERT statement will be stored. dbHandle Specifies a valid database handle. queryString Specifies a valid SQL query. queryProcedure Optional command to execute for each result tuple of a SELECT statement. 2002-03-06 ntuples The number of tuples affected or returned by the query. 2002-03-06 pg_execute submits a query to the PostgreSQL backend. If the query is not a SELECT statement, the query is executed and the number of tuples affected by the query is returned. If the query is an INSERT and a single tuple is inserted, the OID of the inserted tuple is stored in the oidVar variable if the optional -oid argument is supplied. If the query is a SELECT statement, the query is executed. For each tuple in the result, the tuple field values are stored in the arrayVar variable, if supplied, using the field names as the array indexes, else in variables named by the field names, and then the optional queryProcedure is executed if supplied. (Omitting the queryProcedure probably makes sense only if the query will return a single tuple.) The number of tuples selected is returned. The queryProcedure can use the Tcl break, continue, and return commands, with the expected behavior. Note that if the queryProcedure executes return, pg_execute does not return ntuples. pg_execute is a newer function which provides a superset of the features of pg_select, and can replace pg_exec in many cases where access to the result handle is not needed. For backend-handled errors, pg_execute will throw a Tcl error and return two element list. The first element is an error code such as PGRES_FATAL_ERROR, and the second element is the backend error text. For more serious errors, such as failure to communicate with the backend, pg_execute will throw a Tcl error and return just the error message text. In the following examples, error checking with catch has been omitted for clarity. Insert a row and save the OID in result_oid: pg_execute -oid result_oid $pgconn "insert into mytable values (1)" Print the item and value fields from each row: pg_execute -array d $pgconn "select item, value from mytable" { puts "Item=$d(item) Value=$d(value)" } Find the maximum and minimum values and store them in $s(max) and $s(min): pg_execute -array s $pgconn "select max(value) as max,\ min(value) as min from mytable" Find the maximum and minimum values and store them in $max and $min: pg_execute $pgconn "select max(value) as max, min(value) as min from mytable" pg_listen PGTCL - Asynchronous Notify pg_listen set or change 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, 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 PostgreSQL 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 PostgreSQL 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_on_connection_loss PGTCL - Asynchronous Notify pg_on_connection_loss set or change a callback for unexpected connection loss pgtclconnection loss connection loss 2002-09-02 pg_on_connection_loss dbHandle callbackCommand 2002-09-02 dbHandle Specifies a valid database handle. callbackCommand If present, provides the command string to execute when connection loss is detected. 2002-09-02 None 2002-09-02 pg_on_connection_loss creates, changes, or cancels a request to execute a callback command if an unexpected loss of connection to the database occurs. 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. The callback 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_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 and INV_WRITE. The or operator 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 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 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.