The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 478 pages ISBN 9781906966065 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL 9.0 Reference Manual Volume 2 Buy the book here! >>> learn more

1.3.2 Retrieving Query Result Information

These functions are used to extract information from a PGresult object that represents a successful query result (that is, one that has status PGRES_TUPLES_OK). They can also be used to extract information from a successful Describe operation: a Describe's result has all the same column information that actual execution of the query would provide, but it has zero rows. For objects with other status values, these functions will act as though the result has zero rows and zero columns.

PQntuples

Returns the number of rows (tuples) in the query result. Because it returns an integer result, large result sets might overflow the return value on 32-bit operating systems.

int PQntuples(const PGresult *res);

PQnfields

Returns the number of columns (fields) in each row of the query result.

int PQnfields(const PGresult *res);

PQfname

Returns the column name associated with the given column number. Column numbers start at 0. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear.

char *PQfname(const PGresult *res,
              int column_number);

NULL is returned if the column number is out of range.

PQfnumber

Returns the column number associated with the given column name.

int PQfnumber(const PGresult *res,
              const char *column_name);

-1 is returned if the given name does not match any column. The given name is treated like an identifier in an SQL command, that is, it is downcased unless double-quoted. For example, given a query result generated from the SQL command:

SELECT 1 AS FOO, 2 AS "BAR";

we would have the results:

PQfname(res, 0)              foo
PQfname(res, 1)              BAR
PQfnumber(res, "FOO")        0
PQfnumber(res, "foo")        0
PQfnumber(res, "BAR")        -1
PQfnumber(res, "\"BAR\"")    1

PQftable

Returns the OID of the table from which the given column was fetched. Column numbers start at 0.

Oid PQftable(const PGresult *res,
             int column_number);

InvalidOid is returned if the column number is out of range, or if the specified column is not a simple reference to a table column, or when using pre-3.0 protocol. You can query the system table pg_class to determine exactly which table is referenced. The type Oid and the constant InvalidOid will be defined when you include the libpq header file. They will both be some integer type.

PQftablecol

Returns the column number (within its table) of the column making up the specified query result column. Query-result column numbers start at 0, but table columns have nonzero numbers.

int PQftablecol(const PGresult *res,
                int column_number);

Zero is returned if the column number is out of range, or if the specified column is not a simple reference to a table column, or when using pre-3.0 protocol.

PQfformat

Returns the format code indicating the format of the given column. Column numbers start at 0.

int PQfformat(const PGresult *res,
              int column_number);

Format code zero indicates textual data representation, while format code one indicates binary representation. (Other codes are reserved for future definition.)

PQftype

Returns the data type associated with the given column number. The integer returned is the internal OID number of the type. Column numbers start at 0.

Oid PQftype(const PGresult *res,
            int column_number);

You can query the system table pg_type to obtain the names and properties of the various data types. The OIDs of the built-in data types are defined in the file ‘src/include/catalog/pg_type.h’ in the source tree.

PQfmod

Returns the type modifier of the column associated with the given column number. Column numbers start at 0.

int PQfmod(const PGresult *res,
           int column_number);

The interpretation of modifier values is type-specific; they typically indicate precision or size limits. The value -1 is used to indicate “no information available”. Most data types do not use modifiers, in which case the value is always -1.

PQfsize

Returns the size in bytes of the column associated with the given column number. Column numbers start at 0.

int PQfsize(const PGresult *res,
            int column_number);

PQfsize returns the space allocated for this column in a database row, in other words the size of the server's internal representation of the data type. (Accordingly, it is not really very useful to clients.) A negative value indicates the data type is variable-length.

PQbinaryTuples

Returns 1 if the PGresult contains binary data and 0 if it contains text data.

int PQbinaryTuples(const PGresult *res);

This function is deprecated (except for its use in connection with COPY), because it is possible for a single PGresult to contain text data in some columns and binary data in others. PQfformat is preferred. PQbinaryTuples returns 1 only if all columns of the result are binary (format 1).

PQgetvalue

Returns a single field value of one row of a PGresult. Row and column numbers start at 0. The caller should not free the result directly. It will be freed when the associated PGresult handle is passed to PQclear.

char *PQgetvalue(const PGresult *res,
                 int row_number,
                 int column_number);

For data in text format, the value returned by PQgetvalue is a null-terminated character string representation of the field value. For data in binary format, the value is in the binary representation determined by the data type's typsend and typreceive functions. (The value is actually followed by a zero byte in this case too, but that is not ordinarily useful, since the value is likely to contain embedded nulls.) An empty string is returned if the field value is null. See PQgetisnull to distinguish null values from empty-string values. The pointer returned by PQgetvalue points to storage that is part of the PGresult structure. One should not modify the data it points to, and one must explicitly copy the data into other storage if it is to be used past the lifetime of the PGresult structure itself.

PQgetisnull

Tests a field for a null value. Row and column numbers start at 0.

int PQgetisnull(const PGresult *res,
                int row_number,
                int column_number);

This function returns 1 if the field is null and 0 if it contains a non-null value. (Note that PQgetvalue will return an empty string, not a null pointer, for a null field.)

PQgetlength

Returns the actual length of a field value in bytes. Row and column numbers start at 0.

int PQgetlength(const PGresult *res,
                int row_number,
                int column_number);

This is the actual data length for the particular data value, that is, the size of the object pointed to by PQgetvalue. For text data format this is the same as strlen(). For binary format this is essential information. Note that one should not rely on PQfsize to obtain the actual data length.

PQnparams

Returns the number of parameters of a prepared statement.

int PQnparams(const PGresult *res);

This function is only useful when inspecting the result of PQdescribePrepared. For other types of queries the return value is zero.

PQparamtype

Returns the data type of the indicated statement parameter. Parameter numbers start at 0.

Oid PQparamtype(const PGresult *res, int param_number);

This function is only useful when inspecting the result of PQdescribePrepared. For other types of queries the return value is zero.

PQprint

Prints out all the rows and, optionally, the column names to the specified output stream.

void PQprint(FILE * fout, /* output stream */
             const PGresult * res, const PQprintOpt * po);
typedef struct {
  pqbool header;         /* print output field headings and
                            row count */
  pqbool align;          /* fill align the fields */
  pqbool standard;       /* old brain dead format */
  pqbool html3;          /* output HTML tables */
  pqbool expanded;       /* expand tables */
  pqbool pager;          /* use pager for output if needed */
  char *fieldSep;        /* field separator */
  char *tableOpt;        /* attributes for HTML table
                            element */
  char *caption;         /* HTML table caption */
  char **fieldName;      /* null-terminated array of
                            replacement field names */
} PQprintOpt;

This function was formerly used by psql to print query results, but this is no longer the case. Note that it assumes all the data is in text format.