pg_execute
submits a command to the
PostgreSQL server.
If the command is not a SELECT statement, the
number of rows affected by the command is returned. If the command
is an INSERT statement and a single row is
inserted, the OID of the inserted row is stored in the variable
oidVar if the optional -oid
argument is supplied.
If the command is a SELECT statement, then, for each row in the result, the row values are stored in the arrayVar variable, if supplied, using the column names as the array indices, else in variables named by the column names, and then the optional procedure is executed if supplied. (Omitting the procedure probably makes sense only if the query will return a single row.) The number of rows selected is returned.
The procedure can use the Tcl commands
break, continue, and
return with the expected behavior. Note that if
the procedure executes
return, then pg_execute
does not return the number of affected rows.
pg_execute
is a newer command 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.
-array arrayVar
Specifies the name of an array variable where result rows are stored, indexed by the column names. This is ignored if commandString is not a SELECT statement.
-oid oidVar
Specifies the name of a variable into which the OID from an INSERT statement will be stored.
The handle of the connection on which to execute the command.
The SQL command to execute.
Optional procedure to execute for each result row of a SELECT statement.
The number of rows affected or returned by the command.
A Tcl error will be thrown if an error occurs communicating with the database, or if there is an error processing the query. Note that this differs from pg_exec, which will not throw a Tcl error for a query which generates a database error. A Tcl error will also be thrown if a procedure body is supplied and its execution results in a Tcl error.
For database server handled errors, pg_execute
will
throw a Tcl error and the error message will be a two-element list.
The first element is an error code, such as
PGRES_FATAL_ERROR, and
the second element is the database server error text.
Unfortunately, other errors also result in a Tcl error being thrown
but the error message is not a list - just the message text, so
the list form of the error message cannot be assumed.
pg_execute
cannot be used to start a
COPY because it doesn't return a result handle.
You must use pg_exec to start a
COPY.
This command uses or emulates the PostgreSQL
libpq
function PQexec
.
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 $conn "INSERT INTO mytable VALUES (1);"
Print the columns item and value from each row:
pg_execute -array d $conn "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 $conn "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 $conn "SELECT max(value) AS max, min(value) AS min FROM mytable"