Description
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.
Usage
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"
