pg_connect opens a connection to the backend server. A single option string is supplied that can contain multiple option values.
pg_connect -conninfo 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.
If successful, a handle for a database connection is returned. All handles start with the prefix pgsql.
pg_connect -conninfo "dbname=pcheung port=5432 host=localhost" |
pg_disconnect closes a connection to the backend server.
pg_disconnect dbHandle |
A valid connected database handle.
None
pg_disconnect pgsql3 |
pg_conndefaults returns information about thr default connection options available in
pg_connect -conninfo |
pg_conndefaults |
None
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.
pg_conndefaults |
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.
pg_exec dbHandle queryString |
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. Hence, the absence of a Tcl error does not necessarily mean the query completed successfully.
A valid database handle.
A valid SQL query.
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.
pg_exec $conn "SELECT * from table_a" |
pg_result returns information about a query result created by a prior pg_exec.
pg_result resultHandle resultOption |
The handle for a query result.
One of several possible options.
The status of the result.
The error message, if the status indicates error; otherwise an empty string.
The connection that produced the result.
If the command was an INSERT, the OID of the inserted tuple; otherwise an empty string.
The number of tuples returned by the query.
The number of attributes in each tuple.
Assign the results to a list of lists.
Assign the results to an array, using subscripts of the form (tupno,attributeName).
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).
Returns the fields of the indicated tuple in a list. Tuple numbers start at zero.
Stores the fields of the tuple in array arrayName, indexed by field names. Tuple numbers start at zero.
Returns a list of the names of the tuple attributes.
Returns a list of sublists, {name ftype fsize} for each tuple attribute.
Clears the result query object.
The result depends on the selected option, as described above.
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 have created too many query result objects.
set res [pg_exec $conn "SELECT * from table_a"] puts [pg_result $res -status] puts [pg_result $res -numTuples] puts [pg_result $res -getTuple 0] pg_result $res -clear |
pg_select loops over the result of a SELECT statement.
pg_select dbHandle queryString arrayVar queryProcedure |
A valid database handle.
A valid SQL SELECT query.
Array variable for tuples returned.
Procedure run on each tuple found.
The return result is either an error message or a handle for a query result.
pg_select submits a SELECT query to the Red Hat Database 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, before the queryProcedure is executed.
In addition to field values, the following special entities are in the array:
A list of the column names returned.
A number of columns returned.
The following would work if table table_a has at least two fields: name and team.
pg_select $conn "SELECT * from table_a" array {
puts "tuple $array(.tupno) contains columns:
$array(.headers) values: $array(name)
$array(team)"} |
pg_listen sets or changes a callback for asynchronous NOTIFY messages.
pg_listen dbHandle notifyName callbackCommand |
A valid database handle.
The notify condition name to start or stop listening to.
If present and not empty, provides the command string to execute when a matching notification arrives. If empty, stops the listening for notification.
None
pg_listen creates, changes, or cancels a request to listen for asynchronous NOTIFY messages from the backend server. 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 Red Hat Database 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-TkTcl 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.
Example 1:
pg_listen $conn notification {
puts "Received notification"
} |
Example 2:
pg_listen $conn notification {} |
pg_lo_creat creates an Inversion Large Object.
pg_lo_creat conn mode |
A valid database connection.
The access mode for the large object. The mode can be any OR-ing together of INV_READ, INV_WRITE, and INV_ARCHIVE. The OR delimiter character is "|".
The oid of the large object created.
pg_exec $conn "BEGIN" pg_lo_creat $conn "INV_READ|INV_WRITE" pg_exec $conn "END" |
pg_lo_open opens an Inversion Large Object.
pg_lo_open conn objOid mode |
A valid database connection.
A valid large object oid.
The access mode for the large object. The mode can be either r, w, or rw.
A file descriptor for use in later pg_lo* routines.
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] pg_exec $conn "END" |
pg_lo_close closes an Inversion Large Object.
pg_lo_close conn fd |
A valid database connection.
A file descriptor for use in later pg_lo* routines.
None
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] pg_lo_close $conn $fd pg_exec $conn "END" |
pg_lo_read reads at most len bytes from a large object into a variable named bufVar.
pg_lo_read conn fd bufVar len |
A valid database connection.
File descriptor for the large object from pg_lo_open.
A buffer variable to contain the large object segment. bufVar must be a valid variable name.
The maximum allowable size of the large object segment.
None
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] pg_lo_read $conn $fd var 128 puts $var pg_exec $conn "END" |
pg_lo_write writes at most len bytes to a large object from a variable buf.
pg_lo_write conn fd buf len |
A valid database connection.
File descriptor for the large object from pg_lo_open.
A valid string variable to write to the large object.
The maximum size of the string to write.
None
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] pg_lo_write $conn $fd "abc" 3 pg_exec $conn "END" |
pg_lo_lseek positions to offset bytes from the beginning of a large object.
pg_lo_lseek conn fd offset whence |
A valid database connection.
File descriptor for the large object from pg_lo_open.
A zero-based offset in bytes.
whence can be SEEK_CUR, SEEK_END, or SEEK_SET where:
Seek from the current position.
Seek from the end of a file.
Seek from the beginning of a file.
None
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] use pg_lo_seek $conn $fd 0 SEEK_SET pg_exec $conn "END" |
pg_lo_tell returns the current to offset in bytes from the beginning of a large object.
pg_lo_tell conn fd |
A valid database connection.
File descriptor for the large object from pg_lo_open.
A zero-based offset in bytes suitable for input to pg_lo_lseek.
pg_exec $conn "BEGIN" set fd [pg_lo_open $conn $loid r] pg_lo_read $conn $fd var 128 puts $var pg_lo_tell $conn $fd pg_exec $conn "END" |
pg_lo_unlink deletes the specified large object.
pg_lo_unlink conn lobjId |
A valid database connection.
Identifier for a large object.
None
pg_exec $conn "BEGIN" set loid[pg_lo_create $conn "INV_REAP"] pg_lo_unlink $conn $loid pg_exec $conn "END" |
pg_lo_import reads the specified file and places the contents into a large object.
pg_lo_import conn filename |
A valid database connection.
Linux file name.
The oid of the large object imported.
pg_lo_import must be called within a BEGIN/END transaction block.
pg_exec $conn "BEGIN" pg_lo_import $conn /tmp/file1 pg_exec $conn "END" |
pg_lo_export writes the specified large object into a Linux file.
pg_lo_export conn lobjId filename |
A valid database connection.
Large object identifier.
Linux file name.
None
pg_lo_export must be called within a BEGIN/END transaction block.
pg_exec $conn "BEGIN" set loid[pg_lo_import $conn /tmp/file1] pg_lo_export $conn $loid /tmp/file2 pg_exec $conn "END" |