pg_execute [-array arrayVar] [-oid oidVar] dbHandle queryString [queryProcedure]
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.
Specifies the name of a variable into which the OID from an INSERT statement will be stored.
Specifies a valid database handle.
Specifies a valid SQL query.
Optional command to execute for each result tuple of a SELECT statement.
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"