This version of the manual was produced for the
Pgtcl-ng Sourceforge project web service site, which requires the logo on each
page.
To download a logo-free copy of the manual, see the
Pgtcl-ng project
downloads area.
pg_result — Get information about a command result
pg_resultresultHandleresultOption
pg_result returns information about a command result
created by a prior
pg_exec,
pg_exec_prepared,
pg_exec_params,
pg_getresult,
pg_describe_cursor, or
pg_describe_prepared.
You can keep a command 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 may eventually run out of memory or available command
results.
The pgtcl-ng implementation of
the interface has a limit of 128 results per database connection, as a
means of detecting if results are not being freed.
(The pgintcl implementation does not check
for too many active command result objects.)
resultHandleThe handle of the command result.
resultOptionOne of the following options, specifying which piece of result information to return:
-assign arrayName
Assign the results to an array, using subscripts of the form
(rowNumber,columnName).
-assignbyidx arrayName ?appendstr?
Assign the results to an array using the values of the
first column and the names of the remaining column as keys.
If appendstr is given then it is appended to
each key. In short, all but the first column of each row
are stored into the array, using subscripts of the form
(firstColumnValue,columnNameAppendStr).
-attributesReturns a list of the names of the columns in the result.
-clearClear the command result object.
-cmdStatusReturns the command status tag from the SQL command that generated the result. This is the name of the SQL command, such as UPDATE, often followed by additional data such as the number of rows affected. Note: This was added in pgtclng-1.5.1 and in pgintcl-2.0.1.
-cmdTuples
Returns the number of rows (tuples) affected by the command.
This is appropriate to use for commands with completion
status PGRES_COMMAND_OK.
Starting with PostgreSQL-9.0, when used with either
pgintcl-3.2.0 or pgtclng-1.8.0, this will also return
the number of rows from a SELECT query.
However, the use of -numTuples with SELECT
queries is preferred.
-connReturns the handle (name) of the connection that produced the result.
-dictReturns the query results as a Tcl dictionary. The dictionary contains one entry for each result row, using the 0-based row number as the key. The value (for each row) is a dictionary containing result field names as keys, and field values as values. Note: This was added in pgtclng-1.9.0 and in pgintcl-3.3.0. It only works with Tcl-8.5 and higher.
-error ?fieldCode?
Returns the error message, if the status indicates an error,
otherwise an empty string.
Note: the optional fieldCode
parameter was added in
pgintcl-2.2.0 and
pgtclng-1.5.2, making
-error and -errorField
synonymous.
If a fieldCode is supplied,
returns the value of an extended error code field.
Refer to the next option, -errorField
for details.
-errorField ?fieldCode?
Returns the error message, if no
fieldCode is supplied, or the
value of an extended error code field, if
fieldCode is supplied.
Note: the fieldCode
parameter was made optional in
pgintcl-2.2.0 and
pgtclng-1.5.2, making
-error and -errorField
synonymous. Prior to those versions, -error
was used to get the whole error message, and
-errorField was used to get an extended
error code field value.
fieldCode selects the error code
field by name (or alias), or single character abbreviation,
according to the following table.
Note: the FieldCode and Alias are case insensitive,
but the single letter Abbreviations are case sensitive.
| FieldCode | Abbreviation | Alias | Description |
|---|---|---|---|
| SEVERITY | S | Error severity, such as ERROR or FATAL | |
| SQLSTATE | C | 5-character SQL State code | |
| MESSAGE_PRIMARY | M | primary | Primary error message |
| MESSAGE_DETAIL | D | detail | Error message detail, secondary message |
| MESSAGE_HINT | H | hint | Error message hint or suggestion |
| STATEMENT_POSITION | P | position | Decimal integer cursor position |
| CONTEXT | W | Error context, or call stack trace | |
| SOURCE_FILE | F | file | PostgreSQL source code filename |
| SOURCE_LINE | L | line | PostgreSQL source code line number |
| SOURCE_FUNCTION | R | function | PostgreSQL source code function name |
| SCHEMA_NAME | s | Name of the schema of the object associated with the error | |
| TABLE_NAME | t | Name of the table associated with the error | |
| COLUMN_NAME | c | Name of table column associated with the error | |
| DATATYPE_NAME | d | Name of the data type associated with the error | |
| CONSTRAINT_NAME | t | Name of the database constraint associated with the error |
Note: Not all error field codes apply to any given error
condition. pg_result -errorField will
return an empty string if the database server did not
provide the requested error field.
Field code aliases were added to pgintcl-2.2.0 and pgtclng-1.5.2.
Field codes for Schema, Table, Column, Datatype, and Constraint names were added to pgintcl-3.5.0 and pgtclng-2.1.0. Since these field codes use lower-case letter abbreviations (which are defined by the PostgreSQL server, starting with PostgreSQL-9.2 and 9.3), it was necessary to make the abbreviations case sensitive. In earlier releases, the single-character abbreviated field codes were case insensitive.
-getNull rowNumberReturns a list of 1s and 0s for the indicated row, with 1 meaning the value of the column is NULL, and 0 meaning the value of the column is not NULL. Row numbers start at zero.
-getTuple rowNumberReturns the values of the columns of the indicated row in a list. Row numbers start at zero.
-lAttributes
Returns a list of attributes of the query result columns.
For each column, the list contains a sublist of
the form {ColumnName TypeOid TypeSize}.
More information on these values can be found in the
PostgreSQL Libpq documentation.
Note that pg_result -lxAttributes
returns a superset of this information.
-listReturns the entire result as a list of values in row-major, column-minor order.
-llistReturns the entire result as a list of lists. The outer list contains one element for each result row, and the inner lists contain the values for each column of the row.
-lxAttributes
Returns an extended list of attributes of the query result
columns. For each column, the list contains a sublist of
the form {ColumnName TypeOid TypeSize
TypeSizeModifier Format TableOID TableColumnIndex}.
More information on these values can be found in the
PostgreSQL Libpq documentation.
Note that this is an extension of the information returned
by pg_result -lAttributes.
-numAttrsReturns the number of columns (attributes) in each row.
-numParamsReturns the number of parameters in a prepared query, if the result was returned by pg_describe_prepared. Returns 0 for any other type of result. Note: This was added in pgtclng-1.7.0 and in pgintcl-3.1.0.
-numTuples
Returns the number of rows (tuples) returned by the query.
This is appropriate to use for commands with completion
status PGRES_TUPLES_OK.
-oidIf the command was a single row INSERT, returns the OID (Object ID) of the inserted row, if the table has OIDs. The default starting with PostgreSQL 8.0 is to create tables without OIDs. Otherwise returns 0.
-paramTypesReturns a list of the PostgreSQL type OIDs of the parameters in a prepared query, if the result was returned by pg_describe_prepared. Returns an empty list for any other type of result. Note: This was added in pgtclng-1.7.0 and in pgintcl-3.1.0.
-statusReturns the status of the result. This will be one of the following strings:
| Status | Meaning |
|---|---|
| PGRES_COMMAND_OK | Successful completion of a command returning no data, such as INSERT. |
| PGRES_TUPLES_OK | Successful completion of a command which returns data (such as SELECT or SHOW). Note this is the status even if the SELECT happens to return no rows. |
| PGRES_COPY_OUT | Begin COPY TO STDOUT. |
| PGRES_COPY_IN | Begin COPY FROM STDIN. |
| PGRES_EMPTY_QUERY | The query string sent to the server was empty. |
| PGRES_BAD_RESPONSE | The server's response was not understood. |
| PGRES_FATAL_ERROR | An error occurred. This includes any SQL syntax errors, or errors processing the command such as SELECT from a non-existing table. |
-tupleArray rowNumber arrayName
Stores the columns of the row in array
arrayName, indexed by column names.
Row numbers start at zero.
The result depends on the selected option, as described above.
A Tcl error will be thrown if there is an error processing the command, which is unlikely since no communication with the server is involved.
Section 5.4, “Example - The Different Ways to Get Query Results” contains examples of the
different ways to get query results with pg_result.
This command uses a variety of PostgreSQL libpq functions described in the Command Execution Functions chapter of the libpq reference manual.
This version of the manual was produced for the
Pgtcl-ng Sourceforge project web service site, which requires the logo on each
page.
To download a logo-free copy of the manual, see the
Pgtcl-ng project
downloads area.