Robert Simpson wrote:
>> -----Original Message-----
>> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
>> Sent: Monday, February 28, 2005 10:30 AM
>> To: sqlite-users@sqlite.org
>> Subject: RE: [sqlite] ticket 1147
>
> [snip]
>> What do
>> other database engines (PostgreSQL, Oracle, MySQL) do in the way of
>> revealing the originating column for result set values? Do they have
>> some mysterious API that I have never seen?
>
> ODBC : SQLColAttributes() or SQLColAttribute()
> OLEDB: IColumnsRowset interface
> MySQL: mysql_stmt_result_metadata() -- didn't read too much
> into this one, but I think it's the right one
PostgreSQL
for libpq
27.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). For objects with other status values they will act as
though the result has zero rows and zero columns.
PQntuples
Returns the number of rows (tuples) in the query result.
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.
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.
reid
