pg_result

pg_result — Get information about a command result

Synopsis

pg_result resultHandle resultOption

Description

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.)

Arguments

resultHandle

The handle of the command result.

resultOption

One 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).

-attributes

Returns a list of the names of the columns in the result.

-clear

Clear the command result object.

-cmdStatus

Returns 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.

-conn

Returns the handle (name) of the connection that produced the result.

-dict

Returns 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 full name or single character abbreviation, according to the following table.

FieldCodeAbbreviated FieldCodeDescription
SEVERITYSERROR or FATAL, for example
SQLSTATEC5-character SQL State
MESSAGE_PRIMARYMPrimary error message
MESSAGE_DETAILDOptional detailed message
MESSAGE_HINTHOptional suggestion
STATEMENT_POSITIONPDecimal integer cursor position
CONTEXTWCall Stack trace
SOURCE_FILEFPostgreSQL source code filename
SOURCE_LINELPostgreSQL source code line number
SOURCE_FUNCTIONRPostgreSQL source code function name

(Note: 'optional' means the value may or may not be provided by the server.)

In addition, the following field code aliases were added to pgintcl-2.2.0 and pgtclng-1.5.2, for compatibility with Gborg pgtcl. All field code names are accepted in upper or lower case.

FieldCode AliasDescription
primarySame as MESSAGE_PRIMARY
detailSame as MESSAGE_DETAIL
hintSame as MESSAGE_HINT
positionSame as STATEMENT_POSITION
fileSame as SOURCE_FILE
lineSame as SOURCE_LINE
functionSame as SOURCE_FUNCTION

-getNull rowNumber

Returns 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 rowNumber

Returns 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.

-list

Returns the entire result as a list of values in row-major, column-minor order.

-llist

Returns 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.

-numAttrs

Returns the number of columns (attributes) in each row.

-numParams

Returns 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.

-oid

If 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.

-paramTypes

Returns 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.

-status

Returns the status of the result. This will be one of the following strings:

StatusMeaning
PGRES_COMMAND_OKSuccessful completion of a command returning no data, such as INSERT.
PGRES_TUPLES_OKSuccessful 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_OUTBegin COPY TO STDOUT.
PGRES_COPY_INBegin COPY FROM STDIN.
PGRES_EMPTY_QUERYThe query string sent to the server was empty.
PGRES_BAD_RESPONSEThe server's response was not understood.
PGRES_FATAL_ERRORAn 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.

Return Value

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.

Notes

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.