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