Обсуждение: [Fwd: [PATCHES] Libpq support for precision and scale]
This is a patch that was posted some time ago to pgsql-patches and no one has commented on it. It adds something that JDBC has that is not present in libpq (see below). Is it OK for inclusion? Regards to all and thanks for your time, Fernando -------- Original Message -------- From: Fernando Nasser <fnasser@redhat.com> Subject: [PATCHES] Libpq support for precision and scale To: pgsql-patches@postgresql.org Some programs like utilities, IDEs, etc., frequently need to know the precision and scale of the result fields (columns). Unfortunately libpq does not have such routines yet (JDBC does). Liam and I created a few ones that do the trick, as inspired by the JDBC code. The functions are: char *PQftypename(const PGresult *res, int field_num); Returns the type name (not the name of the column, as PQfname do). int PQfprecision(const PGresult *res, int field_num); int PQfscale(const PGresult *res, int field_num); Return Scale and Precision of the type respectively. Most programs won't need this information and may not be willing to pay the overhead for metadata retrieval. Thus, we added an alternative function to be used instead of PQexec if one wishes extra metadata to be retrieved along with the query results: PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); It provides the same functionality and it is used in exactly the same way as PQexec but it includes extra metadata about the result fields. After this cal, it is possible to obtain the precision, scale and type name for each result field. The PQftypename function returns the internal PostgreSQL type name. As some programs may prefer something more user friendly than the internal type names, we've thrown in a conversion routine as well: char *PQtypeint2ext(const char *intname); This routine converts from the internal type name to a more user friendly type name convention. More details are in the patch to the SGML documentation that is part of the patch (attached). -- Liam Stewart <liams@redhat.com> Fernando Nasser <fnasser@redhat.com>Index: fe-connect.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v retrieving revision 1.180 diff -c -p -r1.180 fe-connect.c *** fe-connect.c 2001/11/05 17:46:37 1.180 --- fe-connect.c 2001/11/07 19:00:35 *************** makeEmptyPGconn(void) *** 1849,1854 **** --- 1849,1855 ---- #ifdef USE_SSL conn->allow_ssl_try = TRUE; #endif + conn->typecache = NULL; /* * The output buffer size is set to 8K, which is the usual size of *************** freePGconn(PGconn *conn) *** 1891,1896 **** --- 1892,1898 ---- if (!conn) return; pqClearAsyncResult(conn); /* deallocate result and curTuple */ + pqTypeCacheClear(conn); /* free all type cache entries */ #ifdef USE_SSL if (conn->ssl) SSL_free(conn->ssl); Index: fe-exec.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v retrieving revision 1.113 diff -c -p -r1.113 fe-exec.c *** fe-exec.c 2001/10/25 05:50:13 1.113 --- fe-exec.c 2001/11/07 19:00:35 *************** char *const pgresStatus[] = { *** 48,53 **** --- 48,54 ---- static void pqCatenateResultError(PGresult *res, const char *msg); static void saveErrorResult(PGconn *conn);static PGresult *prepareAsyncResult(PGconn *conn); + static PGresult *pqExec(PGconn *conn, const char *query, int metadata); static int addTuple(PGresult *res, PGresAttValue* tup); static void parseInput(PGconn *conn); static void handleSendFailure(PGconn *conn); *************** static int getRowDescriptions(PGconn *co *** 55,60 **** --- 56,63 ---- static int getAnotherTuple(PGconn *conn, int binary); static int getNotify(PGconn *conn); static int getNotice(PGconn *conn); + static char *pqTypeCacheGet(PGconn *conn, Oid typenum); + static void pqTypeCachePut(PGconn *conn, Oid typenum, char *typename); /* --------------- * Escaping arbitrary stringsto get valid SQL strings/identifiers. *************** addTuple(PGresult *res, PGresAttValue * *** 609,614 **** --- 612,678 ---- return TRUE; } + /* Cache of the correspondence between type Oids and + * type names. Without it too many queries can be made to + * retrieve this same information from the catalog over and over. + */ + + static char * + pqTypeCacheGet(PGconn *conn, Oid typenum) + { + char *typename = NULL; + PGtypecache *tc = conn->typecache; + + /* Look for type Oid. */ + while (tc != NULL) + { + if (tc->typenum == typenum) + { + typename = tc->typename; + break; + } + else + tc = tc->next; + } + return typename; + } + + static void + pqTypeCachePut(PGconn *conn, Oid typenum, char *typename) + { + PGtypecache *typetocache; + + typetocache = (PGtypecache *) malloc(sizeof(PGtypecache)); + if (typetocache == NULL) + { + fprintf(stderr, "pqTypeCachePut: malloc failed.\n"); + return; + } + + typetocache->typenum = typenum; + typetocache->typename = strdup(typename); + typetocache->next = conn->typecache; + conn->typecache = typetocache; + } + + void + pqTypeCacheClear(PGconn *conn) + { + PGtypecache *tc; + PGtypecache *ntc; + + /* Free all tcache entries (and typenames). */ + tc = conn->typecache; + conn->typecache = NULL; + while (tc != NULL) + { + if (tc->typename) + free(tc->typename); + ntc = tc->next; + free(tc); + tc = ntc; + } + } /* * PQsendQuery *************** PQgetResult(PGconn *conn) *** 1277,1301 **** return res; } /* ! * PQexec * send a query to the backend and package up the result in a PGresult * * If the query was not even sent,return NULL; conn->errorMessage is set to * a relevant message. * If the query was sent, a new PGresult is returned(which could indicate * either success or failure). * The user is responsible for freeing the PGresult via PQclear() * when done with it. */ ! PGresult * ! PQexec(PGconn *conn, const char *query) { PGresult *result; PGresult *lastResult; bool savedblocking; /* * we assume anyone calling PQexec wants blocking behaviour, we force --- 1341,1381 ---- return res; } + PGresult * + PQexec(PGconn *conn, const char *query) + { + /* Don't get metadata. */ + return pqExec (conn, query, 0 /* no metadata */); + } + PGresult * + PQexecIncludeMetadata(PGconn *conn, const char *query) + { + /* Get metadata as well. */ + return pqExec (conn, query, 1 /* with metadata */); + } + /* ! * pqExec * send a query to the backend and package up the result in a PGresult * * If the query was not even sent,return NULL; conn->errorMessage is set to * a relevant message. * If the query was sent, a new PGresult is returned(which could indicate * either success or failure). + * If it is called with metadata == 1, the metadata about the column + * results will be obtained and saved in the PGresult. * The user is responsible for freeing the PGresult via PQclear() * when done with it. */ ! static PGresult * ! pqExec(PGconn *conn, const char *query, int metadata) { PGresult *result; PGresult *lastResult; bool savedblocking; + int i; /* * we assume anyone calling PQexec wants blocking behaviour, we force *************** PQexec(PGconn *conn, const char *query) *** 1363,1368 **** --- 1443,1501 ---- if (PQsetnonblocking(conn, savedblocking) == -1) return NULL; + + /* + * If metadata is requested and everything is well, loop through + * the result fields grabing the required information. + */ + + if (metadata && (lastResult->numAttributes > 0)) + for (i = 0; i < lastResult->numAttributes; i++) + { + Oid typenum; + PGresult *result; + char *tempname; + static char query[] = "select typname from pg_type where oid = %lu"; + char *fullquery; + + if ((typenum = lastResult->attDescs[i].typid) == 0) + continue; + + /* Look up the cache for the type name. */ + tempname = pqTypeCacheGet(conn, typenum); + + /* If it is a type that we still don't know the name, + query for the type name and store it in the cache. */ + if (tempname == NULL) + { + fullquery = malloc (sizeof(query) + + 20 /* if Oids become 64 bits */); + if (fullquery == NULL) + { + fprintf(stderr, "pqExec: malloc failed.\n"); + return NULL; + } + /* If the typename was not in the cache, query the catalog + and add it to the cache */ + snprintf(fullquery, sizeof(query) + 20, query, typenum); + result = PQexec(conn, fullquery); + free(fullquery); + if (!result || PQresultStatus(result) != PGRES_TUPLES_OK) + { + PQclear(result); + continue; + } + if (PQntuples(result) != 1 || PQnfields(result) != 1) { + PQclear(result); + continue; + } + pqTypeCachePut(conn, typenum, PQgetvalue(result, 0, 0)); + tempname = pqTypeCacheGet(conn, typenum); + } + + lastResult->attDescs[i].atttypname = strdup(tempname); + } + return lastResult; errout: *************** PQftype(const PGresult *res, int field_n *** 2104,2109 **** --- 2237,2253 ---- return InvalidOid; } + char * + PQftypeName(const PGresult *res, int field_num) + { + if (!check_field_number(res, field_num)) + return NULL; + if (res->attDescs) + return res->attDescs[field_num].atttypname; + else + return NULL; + } + int PQfsize(const PGresult *res, int field_num) { *************** PQfmod(const PGresult *res, int field_nu *** 2124,2129 **** --- 2268,2330 ---- return res->attDescs[field_num].atttypmod; else return 0; + } + + int + PQfprecision(const PGresult *res, int field_num) + { + int mod; + char *type; + + if ((type = PQftypeName(res, field_num)) == NULL) + return 0; + mod = PQfmod(res, field_num); + + if (strcmp(type, "numeric") == 0) + return ((0xFFFF0000) & mod) >> 16; + else if (strcmp(type, "int2") == 0) + return 5; + else if (strcmp(type, "int4") == 0) + return 10; + else if (strcmp(type, "int8") == 0) + return 19; /* It would be 20 if it was unsigned. */ + else if (strcmp(type, "float4") == 0) + return 6; + else if (strcmp(type, "float8") == 0) + return 15; + else if (strcmp(type, "varchar") == 0 || + strcmp(type, "bpchar") == 0 || + strcmp(type, "char") == 0) + return mod - 4; + else if (strcmp(type, "varbit") == 0 || + strcmp(type, "bit") == 0) + return mod; + + return -1; + } + + int + PQfscale(const PGresult *res, int field_num) + { + int mod; + char *type; + + if ((type = PQftypeName(res, field_num)) == NULL) + return 0; + mod = PQfmod(res, field_num); + + if (strcmp(type, "numeric") == 0) + return ((0x0000FFFF) & mod) - 4; + else if (strcmp(type, "int2") == 0 || + strcmp(type, "int4") == 0 || + strcmp(type, "int8") == 0) + return 0; + else if (strcmp(type, "float4") == 0) + return -1; + else if (strcmp(type, "float8") == 0) + return -1; + + return -1; } char * Index: fe-misc.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v retrieving revision 1.60 diff -c -p -r1.60 fe-misc.c *** fe-misc.c 2001/11/05 17:46:37 1.60 --- fe-misc.c 2001/11/07 19:00:35 *************** WSSE_GOODEXIT: *** 896,898 **** --- 896,974 ---- } #endif + + char * + PQinternal2common(const char *intname) + { + static char *typename; + + if (intname == NULL) + return NULL; + + if (strcmp(intname, "int8") == 0) + typename = "bigint"; + else if (strcmp(intname, "bit") == 0) + typename = "bit"; + else if (strcmp(intname, "varbit") == 0) + typename = "varbit"; /* bit varying */ + else if (strcmp(intname, "bool") == 0) + typename = "boolean"; + else if (strcmp(intname, "box") == 0) + typename = "box"; + else if (strcmp(intname, "bpchar") == 0) + typename = "char"; /* character */ + else if (strcmp(intname, "varchar") == 0) + typename = "varchar"; /* character varying */ + else if (strcmp(intname, "cidr") == 0) + typename = "cidr"; + else if (strcmp(intname, "circle") == 0) + typename = "circle"; + else if (strcmp(intname, "date") == 0) + typename = "date"; + else if (strcmp(intname, "float8") == 0) + typename = "double precision"; + else if (strcmp(intname, "inet") == 0) + typename = "inet"; + else if (strcmp(intname, "int4") == 0) + typename = "integer"; + else if (strcmp(intname, "interval") == 0) + typename = "interval"; + else if (strcmp(intname, "line") == 0) + typename = "line"; + else if (strcmp(intname, "lseg") == 0) + typename = "lseg"; + else if (strcmp(intname, "macaddr") == 0) + typename = "macaddr"; + else if (strcmp(intname, "decimal") == 0) + typename = "numeric"; + else if (strcmp(intname, "numeric") == 0) + typename = "numeric"; + else if (strcmp(intname, "oid") == 0) + typename = "oid"; + else if (strcmp(intname, "path") == 0) + typename = "path"; + else if (strcmp(intname, "point") == 0) + typename = "point"; + else if (strcmp(intname, "polygon") == 0) + typename = "polygon"; + else if (strcmp(intname, "float4") == 0) + typename = "real"; + else if (strcmp(intname, "int2") == 0) + typename = "smallint"; + else if (strcmp(intname, "serial") == 0) + typename = "serial"; + else if (strcmp(intname, "text") == 0) + typename = "text"; + else if (strcmp(intname, "time") == 0) + typename = "time"; + else if (strcmp(intname, "time with time zone") == 0) + typename = "time with time zone"; + else if (strcmp(intname, "timestamp") == 0) + typename = "timestamp"; + else if (strcmp(intname, "timestamp with time zone") == 0) + typename = "timestamp with time zone"; + else + typename = NULL; + + return typename; + } Index: libpq-fe.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v retrieving revision 1.79 diff -c -p -r1.79 libpq-fe.h *** libpq-fe.h 2001/11/05 17:46:37 1.79 --- libpq-fe.h 2001/11/07 19:00:35 *************** extern "C" *** 256,261 **** --- 256,262 ---- /* Simple synchronous query */ extern PGresult *PQexec(PGconn *conn, const char *query); + extern PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); extern PGnotify *PQnotifies(PGconn *conn); extern void PQfreeNotify(PGnotify *notify); *************** extern "C" *** 303,315 **** extern char *PQfname(const PGresult *res, int field_num); extern int PQfnumber(const PGresult*res, const char *field_name); extern Oid PQftype(const PGresult *res, int field_num); extern int PQfsize(constPGresult *res, int field_num); extern int PQfmod(const PGresult *res, int field_num); extern char*PQcmdStatus(PGresult *res); extern char *PQoidStatus(const PGresult *res); /* old and ugly */ externOid PQoidValue(const PGresult *res); /* new and improved */ ! extern char *PQcmdTuples(PGresult *res); ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); extern int PQgetlength(const PGresult*res, int tup_num, int field_num); extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); --- 304,319 ---- extern char *PQfname(const PGresult *res, int field_num); extern int PQfnumber(const PGresult*res, const char *field_name); extern Oid PQftype(const PGresult *res, int field_num); + extern char *PQftypeName(const PGresult *res, int field_num); extern int PQfsize(const PGresult *res, intfield_num); extern int PQfmod(const PGresult *res, int field_num); + extern int PQfprecision(const PGresult *res, int field_num); + extern int PQfscale(const PGresult *res, int field_num); extern char *PQcmdStatus(PGresult *res); externchar *PQoidStatus(const PGresult *res); /* old and ugly */ extern Oid PQoidValue(const PGresult *res); /* new and improved */ ! extern char *PQcmdTuples(PGresult *res); ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); extern int PQgetlength(const PGresult*res, int tup_num, int field_num); extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); *************** extern "C" *** 371,376 **** --- 375,383 ---- /* Get encoding id from environment variable PGCLIENTENCODING */ extern int PQenv2encoding(void); + /* Convert internal type name to common type name */ + extern char *PQinternal2common(const char *intname); + #ifdef __cplusplus } #endif Index: libpq-int.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-int.h,v retrieving revision 1.44 diff -c -p -r1.44 libpq-int.h *** libpq-int.h 2001/11/05 17:46:38 1.44 --- libpq-int.h 2001/11/07 19:00:35 *************** union pgresult_data *** 75,88 **** char space[1]; /* dummy for accessing block as bytes */ }; ! /* Data about a single attribute (column) of a query result */ typedef struct pgresAttDesc { ! char *name; /* type name */ Oid typid; /* type id */ int typlen; /* type size */ int atttypmod; /* type-specific modifier info */ } PGresAttDesc; /* Data for a single attribute of a single tuple */ --- 75,91 ---- char space[1]; /* dummy for accessing block as bytes */ }; ! /* Data about a single attribute (column) of a query result. ! * The type name is only available if PQexecIncludeMetadata() was used. ! */ typedef struct pgresAttDesc { ! char *name; /* column name */ Oid typid; /* type id */ int typlen; /* type size */ int atttypmod; /* type-specific modifier info */ + char *atttypname; /* type name */ } PGresAttDesc; /* Data for a single attribute of a single tuple*/ *************** typedef struct pgLobjfuncs *** 191,196 **** --- 194,208 ---- Oid fn_lo_write; /* OID of backend function LOwrite */ } PGlobjfuncs; + /* Entry in the cache of the correspondence between type Oids and type names. + */ + typedef struct pgTypeCache + { + Oid typenum; /* OID of type */ + char *typename; /* name of type */ + struct pgTypeCache *next; /* name of type */ + } PGtypecache; + /* PGconn stores all the state data associated with a single connection * to a backend. */ *************** struct pg_conn *** 240,245 **** --- 252,258 ---- char cryptSalt[2]; /* password salt received from backend */ PGlobjfuncs *lobjfuncs; /* private state for large-object access * fns */ + PGtypecache *typecache; /* cached types for this connection. */ /* Buffer for data received from backendand not yet processed */ char *inBuffer; /* currently allocated buffer */ *************** extern void pqSetResultError(PGresult *r *** 305,310 **** --- 318,324 ---- extern void *pqResultAlloc(PGresult *res, size_t nBytes, bool isBinary); extern char *pqResultStrdup(PGresult*res, const char *str); extern void pqClearAsyncResult(PGconn *conn); + extern void pqTypeCacheClear(PGconn *conn); /* === in fe-misc.c === */ Index: libpq.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.72 diff -c -p -r1.72 libpq.sgml *** libpq.sgml 2001/09/13 15:55:23 1.72 --- libpq.sgml 2001/11/07 19:06:52 *************** PGresult *PQexec(PGconn *conn, *** 728,733 **** --- 728,748 ---- <function>PQerrorMessage</function> to get more information about the error. </para> </listitem> + + <listitem> + <para> + <function>PQexecIncludeMetadata</function> + Submit a query to the server and wait for the result; + include extra metadata about the result fields. + This makes available information such as the type name, + precision and scale for each field in the result. + <synopsis> + PGresult *PQexecIncludeMetadata(PGconn *conn, + const char *query); + </synopsis> + Used the same way as PQexec(). + </para> + </listitem> </itemizedlist> <para> *************** You can query the system table <literal> *** 964,969 **** --- 979,986 ---- the name and properties of the various data types. The <acronym>OID</acronym>s of the built-in data typesare defined in <filename>src/include/catalog/pg_type.h</filename> in the source tree. + The function <function>PQftypename</function> can be used to retrieve the + type name if the result was obtained via <function>PQexecIncludeMetadata</function>. </para> </listitem> *************** extracts data from a <acronym>BINARY</ac *** 1010,1015 **** --- 1027,1126 ---- </para> </listitem> </itemizedlist> + + <para> + The following functions only produce meaningful results if + <function>PQexecIncludeMetadata</function> was used + (as opposed to <function>PQexec</function>). + </para> + + <itemizedlist> + + <listitem> + <para> + <function>PQftypename</function> + Returns the name of the column type as a string. + Field indices start at 0. + <synopsis> + char *PQftypename(const PGresult *res, + int field_index); + </synopsis> + Returns the name of the column type as a string. + Copy the string if needed -- do not modify, free() + or assume its persistence. The internal type name is + returned; use PQtypeint2ext() to convert to a more SQL-ish style. + NULL is returned if the field type name is not availble. + </para> + </listitem> + + <listitem> + <para> + <function>PQfprecision</function> + Returns the precision of the field + associated with the given field index. + Field indices start at 0. + <synopsis> + int PQfprecision(const PGresult *res, + int field_index); + </synopsis> + Returns the precision of the field + associated with the given field index. + For numeric types (INTEGER, FLOAT, etc.), PQfprecision returns the + number of decimal digits in the specified field. For character and bit + string types, such as VARCHAR and BIT, PQfprecision returns the + maximum number of characters/bits allowed in the specified field. + PQfprecision returns 0 if precision information is not available and + -1 if precision is not applicable to the field in question. The latter + will be the case if the type of the field is POINT, for example. + </para> + </listitem> + + <listitem> + <para> + <function>PQfscale</function> + Returns the scale of the field + associated with the given field index. + Field indices start at 0. + <synopsis> + int PQfscale(const PGresult *res, + int field_index); + </synopsis> + Returns the scale of the field + associated with the given field index. + PQfscale returns the scale of the field associated with the given + field index. Scale is the number of digits after the decimal point, + so this function is useful only with fields that are of a numeric + type (INTEGER, FLOAT, NUMERIC, etc.). -1 is returned if scale is not + applicable to the field type. 0 is returned if scale information is + not available. + </para> + </listitem> + </itemizedlist> + + <para> + Use the function below to convert internal type names (like the + ones returned by <function>PQftypename</function>) into something + more user-friendly. + </para> + + <itemizedlist> + <listitem> + <para> + <function>PQtypeint2ext</function> + Converts an internal type name into a SQL-ish + type name. + <synopsis> + char *PQtypeint2ext(const char **intname); + </synopsis> + Converts an internal type name into a SQL-ish + type name. + NULL is returned if the internal type is not recognized + (which will be the case if the type is a UDT). + </para> + </listitem> + + </itemizedlist> + </sect2> <sect2 id="libpq-exec-select-values"> ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Fernando Nasser <fnasser@cygnus.com> writes: > This is a patch that was posted some time ago to pgsql-patches and > no one has commented on it. > It adds something that JDBC has that is not present in libpq (see below). > Is it OK for inclusion? Here are some comments ... > int PQfprecision(const PGresult *res, int field_num); > int PQfscale(const PGresult *res, int field_num); > Return Scale and Precision of the type respectively. These seem okay, but I don't like the API detail that "0 is returned if information is not available". 0 is a valid result, at least for PQfscale. I would recommend returning -1. If you really want to distinguish bad parameters from non-numeric datatype, then return -1 and -2 for those two cases. > Most programs won't need this information and may not be willing > to pay the overhead for metadata retrieval. Thus, we added > an alternative function to be used instead of PQexec if one > wishes extra metadata to be retrieved along with the query > results: > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); This strikes me as very ugly, and unnecessary, and inefficient since it retrieves metadata for all columns even though the client might only need to know about some of them. An even worse problem is that it'll fail entirely with a multi-query query string. What I think would be cleaner would be to do the metadata queries on-the-fly as needed. With the caching that you already have in there, on-the-fly queries wouldn't be any less efficient. But to do a metadata query we must have access to the connection. We could handle it two ways: 1. Add a PGconn parameter to the querying functions. 2. Make use of the PGconn link that's stored in PGresults, and specify that these functions can only be used on PGresults that came from a still-open connection. I think I prefer the first, since it makes it more visible to the programmer that queries may get executed. But it's a judgment call probably; I could see an argument for the second as well. Any comments, anyone? > The PQftypename function returns the internal PostgreSQL type name. > As some programs may prefer something more user friendly than the > internal type names, we've thrown in a conversion routine as well: > char *PQtypeint2ext(const char *intname); > This routine converts from the internal type name to a more user > friendly type name convention. This seems poorly designed. Pass it the type OID and typmod, both of which are readily available from a PQresult without extra computation. That will let you call the backend's format_type ... of course you'll need a PGconn too for that. regards, tom lane
Tom Lane wrote: > > Fernando Nasser <fnasser@cygnus.com> writes: > > This is a patch that was posted some time ago to pgsql-patches and > > no one has commented on it. > > It adds something that JDBC has that is not present in libpq (see below). > > Is it OK for inclusion? > > Here are some comments ... > Thanks. > > int PQfprecision(const PGresult *res, int field_num); > > int PQfscale(const PGresult *res, int field_num); > > > Return Scale and Precision of the type respectively. > > These seem okay, but I don't like the API detail that "0 is returned if > information is not available". 0 is a valid result, at least for > PQfscale. I would recommend returning -1. If you really want to > distinguish bad parameters from non-numeric datatype, then return -1 > and -2 for those two cases. > This seems to be the libpq convention. On calls such as PQfsize and PQfmod, for instance, zero is a valid result and is also returned if the information is not available. Please note that we did not make this convention -- our original version did return -1. But we decided that following a different rule for these two routines was even more confusing. And change the return convention for the whole set of functions at this point seems out of the question. P.S.: Maybe whoever originally designed the libpq interface was trying to accomplish some sort of "soft fail" by returning zero. Just a guess of course. > > Most programs won't need this information and may not be willing > > to pay the overhead for metadata retrieval. Thus, we added > > an alternative function to be used instead of PQexec if one > > wishes extra metadata to be retrieved along with the query > > results: > > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > This strikes me as very ugly, and unnecessary, and inefficient since > it retrieves metadata for all columns even though the client might > only need to know about some of them. This part I would not worry about. The new routines are for result sets (not arbitrary columns) so the fields present in it have already been pre-selected. Also, this kind of information is useful for tools as they don't know beforehand what the fields will be. In all cases we can think of, the tool will always want metadata about all the fields. > An even worse problem is that > it'll fail entirely with a multi-query query string. > This is a bummer. But I see no solution for this besides documenting the restriction in the manual. If I am not mistaken we already have the limitation of returning just the last result anyway (we just collect the error messages). > What I think would be cleaner would be to do the metadata queries > on-the-fly as needed. With the caching that you already have in there, > on-the-fly queries wouldn't be any less efficient. > > But to do a metadata query we must have access to the connection. > We could handle it two ways: > > 1. Add a PGconn parameter to the querying functions. > The problem is that results may be kept longer than connections (see below). The current solution did not require the connection as the metadata is for the result set, not tables. The PGconn parameter would be reasonable for retrieving metadata about table columns, for instance. > 2. Make use of the PGconn link that's stored in PGresults, and > specify that these functions can only be used on PGresults that > came from a still-open connection. > That field has been deprecated (see comments in the source code) because a result may be kept even after the connection is closed. > I think I prefer the first, since it makes it more visible to the > programmer that queries may get executed. But it's a judgment call > probably; I could see an argument for the second as well. Any comments, > anyone? > It would have to be the former (to avoid the stale pointer problem). But requiring a connection adds a restriction to the use of this info and makes it have a different life span than the object it refers to (a PGresult), which is very weird. > > The PQftypename function returns the internal PostgreSQL type name. > > As some programs may prefer something more user friendly than the > > internal type names, we've thrown in a conversion routine as well: > > char *PQtypeint2ext(const char *intname); > > This routine converts from the internal type name to a more user > > friendly type name convention. > > This seems poorly designed. Pass it the type OID and typmod, both of > which are readily available from a PQresult without extra computation. > That will let you call the backend's format_type ... of course you'll > need a PGconn too for that. > Requiring the PGconn is bad. But we still could have a PQFtypeExt() returning the "external" type if people prefer it that way. We thought that this should be kept as an explicit conversion operation to make clear the distinction of what the backend knows about and this outside world view of things. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser <fnasser@redhat.com> writes: > Tom Lane wrote: >> These seem okay, but I don't like the API detail that "0 is returned if >> information is not available". > This seems to be the libpq convention. On calls such as PQfsize and > PQfmod, for instance, zero is a valid result and is also returned if > the information is not available. I don't think zero is (or ever will be) a valid PQfsize result. It was not a valid PQfmod result at the time the routine was written, either, although I think that with Thomas' recent changes it might be possible to see a zero typmod for some of the datetime types. On the other hand -1 is a very common valid result for both PQfsize and PQfmod, so these routines *would* have been broken on day one if they had returned -1. I don't think consistency with other routines that have different ranges of valid results is an adequate argument for making an API that's broken by design. > P.S.: Maybe whoever originally designed the libpq interface was trying > to accomplish some sort of "soft fail" by returning zero. No, they were picking a value that couldn't be mistaken for a valid result. At the time, anyway. >> 2. Make use of the PGconn link that's stored in PGresults, and >> specify that these functions can only be used on PGresults that >> came from a still-open connection. > That field has been deprecated (see comments in the source code) I know; I wrote those comments. But I'd be willing to un-deprecate it if it seemed the most convenient API for the inquiry functions would require it. On the whole though I think passing a PGconn to the metadata inquiry functions would be the right way to go about this. Note that there isn't any fundamental reason to require that it be the same PGconn that was used to acquire the PGresult. Any connection to the same database would do fine. (In fact, for standard types, any connection to a database of the same PG version would do fine...) Anyone else have an opinion? regards, tom lane
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Fernando Nasser wrote: > This is a patch that was posted some time ago to pgsql-patches and > no one has commented on it. > > It adds something that JDBC has that is not present in libpq (see below). > Is it OK for inclusion? > > Regards to all and thanks for your time, > Fernando > > > -------- Original Message -------- > From: Fernando Nasser <fnasser@redhat.com> > Subject: [PATCHES] Libpq support for precision and scale > To: pgsql-patches@postgresql.org > > Some programs like utilities, IDEs, etc., frequently need to know the > precision and scale of the result fields (columns). Unfortunately > libpq does not have such routines yet (JDBC does). > > Liam and I created a few ones that do the trick, as inspired by the > JDBC code. The functions are: > > char *PQftypename(const PGresult *res, int field_num); > > Returns the type name (not the name of the column, as PQfname do). > > > int PQfprecision(const PGresult *res, int field_num); > int PQfscale(const PGresult *res, int field_num); > > Return Scale and Precision of the type respectively. > > > Most programs won't need this information and may not be willing > to pay the overhead for metadata retrieval. Thus, we added > an alternative function to be used instead of PQexec if one > wishes extra metadata to be retrieved along with the query > results: > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > It provides the same functionality and it is used in exactly the > same way as PQexec but it includes extra metadata about the result > fields. After this cal, it is possible to obtain the precision, > scale and type name for each result field. > > > The PQftypename function returns the internal PostgreSQL type name. > As some programs may prefer something more user friendly than the > internal type names, we've thrown in a conversion routine as well: > > char *PQtypeint2ext(const char *intname); > > This routine converts from the internal type name to a more user > friendly type name convention. > > > More details are in the patch to the SGML documentation that is > part of the patch (attached). > > > -- > Liam Stewart <liams@redhat.com> > Fernando Nasser <fnasser@redhat.com> > Index: fe-connect.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v > retrieving revision 1.180 > diff -c -p -r1.180 fe-connect.c > *** fe-connect.c 2001/11/05 17:46:37 1.180 > --- fe-connect.c 2001/11/07 19:00:35 > *************** makeEmptyPGconn(void) > *** 1849,1854 **** > --- 1849,1855 ---- > #ifdef USE_SSL > conn->allow_ssl_try = TRUE; > #endif > + conn->typecache = NULL; > > /* > * The output buffer size is set to 8K, which is the usual size of > *************** freePGconn(PGconn *conn) > *** 1891,1896 **** > --- 1892,1898 ---- > if (!conn) > return; > pqClearAsyncResult(conn); /* deallocate result and curTuple */ > + pqTypeCacheClear(conn); /* free all type cache entries */ > #ifdef USE_SSL > if (conn->ssl) > SSL_free(conn->ssl); > Index: fe-exec.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v > retrieving revision 1.113 > diff -c -p -r1.113 fe-exec.c > *** fe-exec.c 2001/10/25 05:50:13 1.113 > --- fe-exec.c 2001/11/07 19:00:35 > *************** char *const pgresStatus[] = { > *** 48,53 **** > --- 48,54 ---- > static void pqCatenateResultError(PGresult *res, const char *msg); > static void saveErrorResult(PGconn *conn); > static PGresult *prepareAsyncResult(PGconn *conn); > + static PGresult *pqExec(PGconn *conn, const char *query, int metadata); > static int addTuple(PGresult *res, PGresAttValue * tup); > static void parseInput(PGconn *conn); > static void handleSendFailure(PGconn *conn); > *************** static int getRowDescriptions(PGconn *co > *** 55,60 **** > --- 56,63 ---- > static int getAnotherTuple(PGconn *conn, int binary); > static int getNotify(PGconn *conn); > static int getNotice(PGconn *conn); > + static char *pqTypeCacheGet(PGconn *conn, Oid typenum); > + static void pqTypeCachePut(PGconn *conn, Oid typenum, char *typename); > > /* --------------- > * Escaping arbitrary strings to get valid SQL strings/identifiers. > *************** addTuple(PGresult *res, PGresAttValue * > *** 609,614 **** > --- 612,678 ---- > return TRUE; > } > > + /* Cache of the correspondence between type Oids and > + * type names. Without it too many queries can be made to > + * retrieve this same information from the catalog over and over. > + */ > + > + static char * > + pqTypeCacheGet(PGconn *conn, Oid typenum) > + { > + char *typename = NULL; > + PGtypecache *tc = conn->typecache; > + > + /* Look for type Oid. */ > + while (tc != NULL) > + { > + if (tc->typenum == typenum) > + { > + typename = tc->typename; > + break; > + } > + else > + tc = tc->next; > + } > + return typename; > + } > + > + static void > + pqTypeCachePut(PGconn *conn, Oid typenum, char *typename) > + { > + PGtypecache *typetocache; > + > + typetocache = (PGtypecache *) malloc(sizeof(PGtypecache)); > + if (typetocache == NULL) > + { > + fprintf(stderr, "pqTypeCachePut: malloc failed.\n"); > + return; > + } > + > + typetocache->typenum = typenum; > + typetocache->typename = strdup(typename); > + typetocache->next = conn->typecache; > + conn->typecache = typetocache; > + } > + > + void > + pqTypeCacheClear(PGconn *conn) > + { > + PGtypecache *tc; > + PGtypecache *ntc; > + > + /* Free all tcache entries (and typenames). */ > + tc = conn->typecache; > + conn->typecache = NULL; > + while (tc != NULL) > + { > + if (tc->typename) > + free(tc->typename); > + ntc = tc->next; > + free(tc); > + tc = ntc; > + } > + } > > /* > * PQsendQuery > *************** PQgetResult(PGconn *conn) > *** 1277,1301 **** > return res; > } > > > /* > ! * PQexec > * send a query to the backend and package up the result in a PGresult > * > * If the query was not even sent, return NULL; conn->errorMessage is set to > * a relevant message. > * If the query was sent, a new PGresult is returned (which could indicate > * either success or failure). > * The user is responsible for freeing the PGresult via PQclear() > * when done with it. > */ > > ! PGresult * > ! PQexec(PGconn *conn, const char *query) > { > PGresult *result; > PGresult *lastResult; > bool savedblocking; > > /* > * we assume anyone calling PQexec wants blocking behaviour, we force > --- 1341,1381 ---- > return res; > } > > + PGresult * > + PQexec(PGconn *conn, const char *query) > + { > + /* Don't get metadata. */ > + return pqExec (conn, query, 0 /* no metadata */); > + } > > + PGresult * > + PQexecIncludeMetadata(PGconn *conn, const char *query) > + { > + /* Get metadata as well. */ > + return pqExec (conn, query, 1 /* with metadata */); > + } > + > /* > ! * pqExec > * send a query to the backend and package up the result in a PGresult > * > * If the query was not even sent, return NULL; conn->errorMessage is set to > * a relevant message. > * If the query was sent, a new PGresult is returned (which could indicate > * either success or failure). > + * If it is called with metadata == 1, the metadata about the column > + * results will be obtained and saved in the PGresult. > * The user is responsible for freeing the PGresult via PQclear() > * when done with it. > */ > > ! static PGresult * > ! pqExec(PGconn *conn, const char *query, int metadata) > { > PGresult *result; > PGresult *lastResult; > bool savedblocking; > + int i; > > /* > * we assume anyone calling PQexec wants blocking behaviour, we force > *************** PQexec(PGconn *conn, const char *query) > *** 1363,1368 **** > --- 1443,1501 ---- > > if (PQsetnonblocking(conn, savedblocking) == -1) > return NULL; > + > + /* > + * If metadata is requested and everything is well, loop through > + * the result fields grabing the required information. > + */ > + > + if (metadata && (lastResult->numAttributes > 0)) > + for (i = 0; i < lastResult->numAttributes; i++) > + { > + Oid typenum; > + PGresult *result; > + char *tempname; > + static char query[] = "select typname from pg_type where oid = %lu"; > + char *fullquery; > + > + if ((typenum = lastResult->attDescs[i].typid) == 0) > + continue; > + > + /* Look up the cache for the type name. */ > + tempname = pqTypeCacheGet(conn, typenum); > + > + /* If it is a type that we still don't know the name, > + query for the type name and store it in the cache. */ > + if (tempname == NULL) > + { > + fullquery = malloc (sizeof(query) > + + 20 /* if Oids become 64 bits */); > + if (fullquery == NULL) > + { > + fprintf(stderr, "pqExec: malloc failed.\n"); > + return NULL; > + } > + /* If the typename was not in the cache, query the catalog > + and add it to the cache */ > + snprintf(fullquery, sizeof(query) + 20, query, typenum); > + result = PQexec(conn, fullquery); > + free(fullquery); > + if (!result || PQresultStatus(result) != PGRES_TUPLES_OK) > + { > + PQclear(result); > + continue; > + } > + if (PQntuples(result) != 1 || PQnfields(result) != 1) { > + PQclear(result); > + continue; > + } > + pqTypeCachePut(conn, typenum, PQgetvalue(result, 0, 0)); > + tempname = pqTypeCacheGet(conn, typenum); > + } > + > + lastResult->attDescs[i].atttypname = strdup(tempname); > + } > + > return lastResult; > > errout: > *************** PQftype(const PGresult *res, int field_n > *** 2104,2109 **** > --- 2237,2253 ---- > return InvalidOid; > } > > + char * > + PQftypeName(const PGresult *res, int field_num) > + { > + if (!check_field_number(res, field_num)) > + return NULL; > + if (res->attDescs) > + return res->attDescs[field_num].atttypname; > + else > + return NULL; > + } > + > int > PQfsize(const PGresult *res, int field_num) > { > *************** PQfmod(const PGresult *res, int field_nu > *** 2124,2129 **** > --- 2268,2330 ---- > return res->attDescs[field_num].atttypmod; > else > return 0; > + } > + > + int > + PQfprecision(const PGresult *res, int field_num) > + { > + int mod; > + char *type; > + > + if ((type = PQftypeName(res, field_num)) == NULL) > + return 0; > + mod = PQfmod(res, field_num); > + > + if (strcmp(type, "numeric") == 0) > + return ((0xFFFF0000) & mod) >> 16; > + else if (strcmp(type, "int2") == 0) > + return 5; > + else if (strcmp(type, "int4") == 0) > + return 10; > + else if (strcmp(type, "int8") == 0) > + return 19; /* It would be 20 if it was unsigned. */ > + else if (strcmp(type, "float4") == 0) > + return 6; > + else if (strcmp(type, "float8") == 0) > + return 15; > + else if (strcmp(type, "varchar") == 0 || > + strcmp(type, "bpchar") == 0 || > + strcmp(type, "char") == 0) > + return mod - 4; > + else if (strcmp(type, "varbit") == 0 || > + strcmp(type, "bit") == 0) > + return mod; > + > + return -1; > + } > + > + int > + PQfscale(const PGresult *res, int field_num) > + { > + int mod; > + char *type; > + > + if ((type = PQftypeName(res, field_num)) == NULL) > + return 0; > + mod = PQfmod(res, field_num); > + > + if (strcmp(type, "numeric") == 0) > + return ((0x0000FFFF) & mod) - 4; > + else if (strcmp(type, "int2") == 0 || > + strcmp(type, "int4") == 0 || > + strcmp(type, "int8") == 0) > + return 0; > + else if (strcmp(type, "float4") == 0) > + return -1; > + else if (strcmp(type, "float8") == 0) > + return -1; > + > + return -1; > } > > char * > Index: fe-misc.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v > retrieving revision 1.60 > diff -c -p -r1.60 fe-misc.c > *** fe-misc.c 2001/11/05 17:46:37 1.60 > --- fe-misc.c 2001/11/07 19:00:35 > *************** WSSE_GOODEXIT: > *** 896,898 **** > --- 896,974 ---- > } > > #endif > + > + char * > + PQinternal2common(const char *intname) > + { > + static char *typename; > + > + if (intname == NULL) > + return NULL; > + > + if (strcmp(intname, "int8") == 0) > + typename = "bigint"; > + else if (strcmp(intname, "bit") == 0) > + typename = "bit"; > + else if (strcmp(intname, "varbit") == 0) > + typename = "varbit"; /* bit varying */ > + else if (strcmp(intname, "bool") == 0) > + typename = "boolean"; > + else if (strcmp(intname, "box") == 0) > + typename = "box"; > + else if (strcmp(intname, "bpchar") == 0) > + typename = "char"; /* character */ > + else if (strcmp(intname, "varchar") == 0) > + typename = "varchar"; /* character varying */ > + else if (strcmp(intname, "cidr") == 0) > + typename = "cidr"; > + else if (strcmp(intname, "circle") == 0) > + typename = "circle"; > + else if (strcmp(intname, "date") == 0) > + typename = "date"; > + else if (strcmp(intname, "float8") == 0) > + typename = "double precision"; > + else if (strcmp(intname, "inet") == 0) > + typename = "inet"; > + else if (strcmp(intname, "int4") == 0) > + typename = "integer"; > + else if (strcmp(intname, "interval") == 0) > + typename = "interval"; > + else if (strcmp(intname, "line") == 0) > + typename = "line"; > + else if (strcmp(intname, "lseg") == 0) > + typename = "lseg"; > + else if (strcmp(intname, "macaddr") == 0) > + typename = "macaddr"; > + else if (strcmp(intname, "decimal") == 0) > + typename = "numeric"; > + else if (strcmp(intname, "numeric") == 0) > + typename = "numeric"; > + else if (strcmp(intname, "oid") == 0) > + typename = "oid"; > + else if (strcmp(intname, "path") == 0) > + typename = "path"; > + else if (strcmp(intname, "point") == 0) > + typename = "point"; > + else if (strcmp(intname, "polygon") == 0) > + typename = "polygon"; > + else if (strcmp(intname, "float4") == 0) > + typename = "real"; > + else if (strcmp(intname, "int2") == 0) > + typename = "smallint"; > + else if (strcmp(intname, "serial") == 0) > + typename = "serial"; > + else if (strcmp(intname, "text") == 0) > + typename = "text"; > + else if (strcmp(intname, "time") == 0) > + typename = "time"; > + else if (strcmp(intname, "time with time zone") == 0) > + typename = "time with time zone"; > + else if (strcmp(intname, "timestamp") == 0) > + typename = "timestamp"; > + else if (strcmp(intname, "timestamp with time zone") == 0) > + typename = "timestamp with time zone"; > + else > + typename = NULL; > + > + return typename; > + } > Index: libpq-fe.h > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v > retrieving revision 1.79 > diff -c -p -r1.79 libpq-fe.h > *** libpq-fe.h 2001/11/05 17:46:37 1.79 > --- libpq-fe.h 2001/11/07 19:00:35 > *************** extern "C" > *** 256,261 **** > --- 256,262 ---- > > /* Simple synchronous query */ > extern PGresult *PQexec(PGconn *conn, const char *query); > + extern PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > extern PGnotify *PQnotifies(PGconn *conn); > extern void PQfreeNotify(PGnotify *notify); > > *************** extern "C" > *** 303,315 **** > extern char *PQfname(const PGresult *res, int field_num); > extern int PQfnumber(const PGresult *res, const char *field_name); > extern Oid PQftype(const PGresult *res, int field_num); > extern int PQfsize(const PGresult *res, int field_num); > extern int PQfmod(const PGresult *res, int field_num); > extern char *PQcmdStatus(PGresult *res); > extern char *PQoidStatus(const PGresult *res); /* old and ugly */ > extern Oid PQoidValue(const PGresult *res); /* new and improved */ > ! extern char *PQcmdTuples(PGresult *res); > ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); > extern int PQgetlength(const PGresult *res, int tup_num, int field_num); > extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); > > --- 304,319 ---- > extern char *PQfname(const PGresult *res, int field_num); > extern int PQfnumber(const PGresult *res, const char *field_name); > extern Oid PQftype(const PGresult *res, int field_num); > + extern char *PQftypeName(const PGresult *res, int field_num); > extern int PQfsize(const PGresult *res, int field_num); > extern int PQfmod(const PGresult *res, int field_num); > + extern int PQfprecision(const PGresult *res, int field_num); > + extern int PQfscale(const PGresult *res, int field_num); > extern char *PQcmdStatus(PGresult *res); > extern char *PQoidStatus(const PGresult *res); /* old and ugly */ > extern Oid PQoidValue(const PGresult *res); /* new and improved */ > ! extern char *PQcmdTuples(PGresult *res); > ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); > extern int PQgetlength(const PGresult *res, int tup_num, int field_num); > extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); > > *************** extern "C" > *** 371,376 **** > --- 375,383 ---- > /* Get encoding id from environment variable PGCLIENTENCODING */ > extern int PQenv2encoding(void); > > + /* Convert internal type name to common type name */ > + extern char *PQinternal2common(const char *intname); > + > #ifdef __cplusplus > } > #endif > Index: libpq-int.h > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-int.h,v > retrieving revision 1.44 > diff -c -p -r1.44 libpq-int.h > *** libpq-int.h 2001/11/05 17:46:38 1.44 > --- libpq-int.h 2001/11/07 19:00:35 > *************** union pgresult_data > *** 75,88 **** > char space[1]; /* dummy for accessing block as bytes */ > }; > > ! /* Data about a single attribute (column) of a query result */ > > typedef struct pgresAttDesc > { > ! char *name; /* type name */ > Oid typid; /* type id */ > int typlen; /* type size */ > int atttypmod; /* type-specific modifier info */ > } PGresAttDesc; > > /* Data for a single attribute of a single tuple */ > --- 75,91 ---- > char space[1]; /* dummy for accessing block as bytes */ > }; > > ! /* Data about a single attribute (column) of a query result. > ! * The type name is only available if PQexecIncludeMetadata() was used. > ! */ > > typedef struct pgresAttDesc > { > ! char *name; /* column name */ > Oid typid; /* type id */ > int typlen; /* type size */ > int atttypmod; /* type-specific modifier info */ > + char *atttypname; /* type name */ > } PGresAttDesc; > > /* Data for a single attribute of a single tuple */ > *************** typedef struct pgLobjfuncs > *** 191,196 **** > --- 194,208 ---- > Oid fn_lo_write; /* OID of backend function LOwrite */ > } PGlobjfuncs; > > + /* Entry in the cache of the correspondence between type Oids and type names. > + */ > + typedef struct pgTypeCache > + { > + Oid typenum; /* OID of type */ > + char *typename; /* name of type */ > + struct pgTypeCache *next; /* name of type */ > + } PGtypecache; > + > /* PGconn stores all the state data associated with a single connection > * to a backend. > */ > *************** struct pg_conn > *** 240,245 **** > --- 252,258 ---- > char cryptSalt[2]; /* password salt received from backend */ > PGlobjfuncs *lobjfuncs; /* private state for large-object access > * fns */ > + PGtypecache *typecache; /* cached types for this connection. */ > > /* Buffer for data received from backend and not yet processed */ > char *inBuffer; /* currently allocated buffer */ > *************** extern void pqSetResultError(PGresult *r > *** 305,310 **** > --- 318,324 ---- > extern void *pqResultAlloc(PGresult *res, size_t nBytes, bool isBinary); > extern char *pqResultStrdup(PGresult *res, const char *str); > extern void pqClearAsyncResult(PGconn *conn); > + extern void pqTypeCacheClear(PGconn *conn); > > /* === in fe-misc.c === */ > > > Index: libpq.sgml > =================================================================== > RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v > retrieving revision 1.72 > diff -c -p -r1.72 libpq.sgml > *** libpq.sgml 2001/09/13 15:55:23 1.72 > --- libpq.sgml 2001/11/07 19:06:52 > *************** PGresult *PQexec(PGconn *conn, > *** 728,733 **** > --- 728,748 ---- > <function>PQerrorMessage</function> to get more information about the error. > </para> > </listitem> > + > + <listitem> > + <para> > + <function>PQexecIncludeMetadata</function> > + Submit a query to the server and wait for the result; > + include extra metadata about the result fields. > + This makes available information such as the type name, > + precision and scale for each field in the result. > + <synopsis> > + PGresult *PQexecIncludeMetadata(PGconn *conn, > + const char *query); > + </synopsis> > + Used the same way as PQexec(). > + </para> > + </listitem> > </itemizedlist> > > <para> > *************** You can query the system table <literal> > *** 964,969 **** > --- 979,986 ---- > the name and properties of the various data types. The <acronym>OID</acronym>s > of the built-in data types are defined in <filename>src/include/catalog/pg_type.h</filename> > in the source tree. > + The function <function>PQftypename</function> can be used to retrieve the > + type name if the result was obtained via <function>PQexecIncludeMetadata</function>. > </para> > </listitem> > > *************** extracts data from a <acronym>BINARY</ac > *** 1010,1015 **** > --- 1027,1126 ---- > </para> > </listitem> > </itemizedlist> > + > + <para> > + The following functions only produce meaningful results if > + <function>PQexecIncludeMetadata</function> was used > + (as opposed to <function>PQexec</function>). > + </para> > + > + <itemizedlist> > + > + <listitem> > + <para> > + <function>PQftypename</function> > + Returns the name of the column type as a string. > + Field indices start at 0. > + <synopsis> > + char *PQftypename(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the name of the column type as a string. > + Copy the string if needed -- do not modify, free() > + or assume its persistence. The internal type name is > + returned; use PQtypeint2ext() to convert to a more SQL-ish style. > + NULL is returned if the field type name is not availble. > + </para> > + </listitem> > + > + <listitem> > + <para> > + <function>PQfprecision</function> > + Returns the precision of the field > + associated with the given field index. > + Field indices start at 0. > + <synopsis> > + int PQfprecision(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the precision of the field > + associated with the given field index. > + For numeric types (INTEGER, FLOAT, etc.), PQfprecision returns the > + number of decimal digits in the specified field. For character and bit > + string types, such as VARCHAR and BIT, PQfprecision returns the > + maximum number of characters/bits allowed in the specified field. > + PQfprecision returns 0 if precision information is not available and > + -1 if precision is not applicable to the field in question. The latter > + will be the case if the type of the field is POINT, for example. > + </para> > + </listitem> > + > + <listitem> > + <para> > + <function>PQfscale</function> > + Returns the scale of the field > + associated with the given field index. > + Field indices start at 0. > + <synopsis> > + int PQfscale(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the scale of the field > + associated with the given field index. > + PQfscale returns the scale of the field associated with the given > + field index. Scale is the number of digits after the decimal point, > + so this function is useful only with fields that are of a numeric > + type (INTEGER, FLOAT, NUMERIC, etc.). -1 is returned if scale is not > + applicable to the field type. 0 is returned if scale information is > + not available. > + </para> > + </listitem> > + </itemizedlist> > + > + <para> > + Use the function below to convert internal type names (like the > + ones returned by <function>PQftypename</function>) into something > + more user-friendly. > + </para> > + > + <itemizedlist> > + <listitem> > + <para> > + <function>PQtypeint2ext</function> > + Converts an internal type name into a SQL-ish > + type name. > + <synopsis> > + char *PQtypeint2ext(const char **intname); > + </synopsis> > + Converts an internal type name into a SQL-ish > + type name. > + NULL is returned if the internal type is not recognized > + (which will be the case if the type is a UDT). > + </para> > + </listitem> > + > + </itemizedlist> > + > </sect2> > > <sect2 id="libpq-exec-select-values"> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Sorry, I see later comments questioning the patch. Please review and resubmit: http://candle.pha.pa.us/cgi-bin/pgpatches2 --------------------------------------------------------------------------- Fernando Nasser wrote: > This is a patch that was posted some time ago to pgsql-patches and > no one has commented on it. > > It adds something that JDBC has that is not present in libpq (see below). > Is it OK for inclusion? > > Regards to all and thanks for your time, > Fernando > > > -------- Original Message -------- > From: Fernando Nasser <fnasser@redhat.com> > Subject: [PATCHES] Libpq support for precision and scale > To: pgsql-patches@postgresql.org > > Some programs like utilities, IDEs, etc., frequently need to know the > precision and scale of the result fields (columns). Unfortunately > libpq does not have such routines yet (JDBC does). > > Liam and I created a few ones that do the trick, as inspired by the > JDBC code. The functions are: > > char *PQftypename(const PGresult *res, int field_num); > > Returns the type name (not the name of the column, as PQfname do). > > > int PQfprecision(const PGresult *res, int field_num); > int PQfscale(const PGresult *res, int field_num); > > Return Scale and Precision of the type respectively. > > > Most programs won't need this information and may not be willing > to pay the overhead for metadata retrieval. Thus, we added > an alternative function to be used instead of PQexec if one > wishes extra metadata to be retrieved along with the query > results: > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > It provides the same functionality and it is used in exactly the > same way as PQexec but it includes extra metadata about the result > fields. After this cal, it is possible to obtain the precision, > scale and type name for each result field. > > > The PQftypename function returns the internal PostgreSQL type name. > As some programs may prefer something more user friendly than the > internal type names, we've thrown in a conversion routine as well: > > char *PQtypeint2ext(const char *intname); > > This routine converts from the internal type name to a more user > friendly type name convention. > > > More details are in the patch to the SGML documentation that is > part of the patch (attached). > > > -- > Liam Stewart <liams@redhat.com> > Fernando Nasser <fnasser@redhat.com> -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Sorry, this patch has been rejected. Please continue discussion on the hackers list. Thank you. I think we do need this functionality somehow. --------------------------------------------------------------------------- Fernando Nasser wrote: > This is a patch that was posted some time ago to pgsql-patches and > no one has commented on it. > > It adds something that JDBC has that is not present in libpq (see below). > Is it OK for inclusion? > > Regards to all and thanks for your time, > Fernando > > > -------- Original Message -------- > From: Fernando Nasser <fnasser@redhat.com> > Subject: [PATCHES] Libpq support for precision and scale > To: pgsql-patches@postgresql.org > > Some programs like utilities, IDEs, etc., frequently need to know the > precision and scale of the result fields (columns). Unfortunately > libpq does not have such routines yet (JDBC does). > > Liam and I created a few ones that do the trick, as inspired by the > JDBC code. The functions are: > > char *PQftypename(const PGresult *res, int field_num); > > Returns the type name (not the name of the column, as PQfname do). > > > int PQfprecision(const PGresult *res, int field_num); > int PQfscale(const PGresult *res, int field_num); > > Return Scale and Precision of the type respectively. > > > Most programs won't need this information and may not be willing > to pay the overhead for metadata retrieval. Thus, we added > an alternative function to be used instead of PQexec if one > wishes extra metadata to be retrieved along with the query > results: > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > It provides the same functionality and it is used in exactly the > same way as PQexec but it includes extra metadata about the result > fields. After this cal, it is possible to obtain the precision, > scale and type name for each result field. > > > The PQftypename function returns the internal PostgreSQL type name. > As some programs may prefer something more user friendly than the > internal type names, we've thrown in a conversion routine as well: > > char *PQtypeint2ext(const char *intname); > > This routine converts from the internal type name to a more user > friendly type name convention. > > > More details are in the patch to the SGML documentation that is > part of the patch (attached). > > > -- > Liam Stewart <liams@redhat.com> > Fernando Nasser <fnasser@redhat.com> > Index: fe-connect.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v > retrieving revision 1.180 > diff -c -p -r1.180 fe-connect.c > *** fe-connect.c 2001/11/05 17:46:37 1.180 > --- fe-connect.c 2001/11/07 19:00:35 > *************** makeEmptyPGconn(void) > *** 1849,1854 **** > --- 1849,1855 ---- > #ifdef USE_SSL > conn->allow_ssl_try = TRUE; > #endif > + conn->typecache = NULL; > > /* > * The output buffer size is set to 8K, which is the usual size of > *************** freePGconn(PGconn *conn) > *** 1891,1896 **** > --- 1892,1898 ---- > if (!conn) > return; > pqClearAsyncResult(conn); /* deallocate result and curTuple */ > + pqTypeCacheClear(conn); /* free all type cache entries */ > #ifdef USE_SSL > if (conn->ssl) > SSL_free(conn->ssl); > Index: fe-exec.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-exec.c,v > retrieving revision 1.113 > diff -c -p -r1.113 fe-exec.c > *** fe-exec.c 2001/10/25 05:50:13 1.113 > --- fe-exec.c 2001/11/07 19:00:35 > *************** char *const pgresStatus[] = { > *** 48,53 **** > --- 48,54 ---- > static void pqCatenateResultError(PGresult *res, const char *msg); > static void saveErrorResult(PGconn *conn); > static PGresult *prepareAsyncResult(PGconn *conn); > + static PGresult *pqExec(PGconn *conn, const char *query, int metadata); > static int addTuple(PGresult *res, PGresAttValue * tup); > static void parseInput(PGconn *conn); > static void handleSendFailure(PGconn *conn); > *************** static int getRowDescriptions(PGconn *co > *** 55,60 **** > --- 56,63 ---- > static int getAnotherTuple(PGconn *conn, int binary); > static int getNotify(PGconn *conn); > static int getNotice(PGconn *conn); > + static char *pqTypeCacheGet(PGconn *conn, Oid typenum); > + static void pqTypeCachePut(PGconn *conn, Oid typenum, char *typename); > > /* --------------- > * Escaping arbitrary strings to get valid SQL strings/identifiers. > *************** addTuple(PGresult *res, PGresAttValue * > *** 609,614 **** > --- 612,678 ---- > return TRUE; > } > > + /* Cache of the correspondence between type Oids and > + * type names. Without it too many queries can be made to > + * retrieve this same information from the catalog over and over. > + */ > + > + static char * > + pqTypeCacheGet(PGconn *conn, Oid typenum) > + { > + char *typename = NULL; > + PGtypecache *tc = conn->typecache; > + > + /* Look for type Oid. */ > + while (tc != NULL) > + { > + if (tc->typenum == typenum) > + { > + typename = tc->typename; > + break; > + } > + else > + tc = tc->next; > + } > + return typename; > + } > + > + static void > + pqTypeCachePut(PGconn *conn, Oid typenum, char *typename) > + { > + PGtypecache *typetocache; > + > + typetocache = (PGtypecache *) malloc(sizeof(PGtypecache)); > + if (typetocache == NULL) > + { > + fprintf(stderr, "pqTypeCachePut: malloc failed.\n"); > + return; > + } > + > + typetocache->typenum = typenum; > + typetocache->typename = strdup(typename); > + typetocache->next = conn->typecache; > + conn->typecache = typetocache; > + } > + > + void > + pqTypeCacheClear(PGconn *conn) > + { > + PGtypecache *tc; > + PGtypecache *ntc; > + > + /* Free all tcache entries (and typenames). */ > + tc = conn->typecache; > + conn->typecache = NULL; > + while (tc != NULL) > + { > + if (tc->typename) > + free(tc->typename); > + ntc = tc->next; > + free(tc); > + tc = ntc; > + } > + } > > /* > * PQsendQuery > *************** PQgetResult(PGconn *conn) > *** 1277,1301 **** > return res; > } > > > /* > ! * PQexec > * send a query to the backend and package up the result in a PGresult > * > * If the query was not even sent, return NULL; conn->errorMessage is set to > * a relevant message. > * If the query was sent, a new PGresult is returned (which could indicate > * either success or failure). > * The user is responsible for freeing the PGresult via PQclear() > * when done with it. > */ > > ! PGresult * > ! PQexec(PGconn *conn, const char *query) > { > PGresult *result; > PGresult *lastResult; > bool savedblocking; > > /* > * we assume anyone calling PQexec wants blocking behaviour, we force > --- 1341,1381 ---- > return res; > } > > + PGresult * > + PQexec(PGconn *conn, const char *query) > + { > + /* Don't get metadata. */ > + return pqExec (conn, query, 0 /* no metadata */); > + } > > + PGresult * > + PQexecIncludeMetadata(PGconn *conn, const char *query) > + { > + /* Get metadata as well. */ > + return pqExec (conn, query, 1 /* with metadata */); > + } > + > /* > ! * pqExec > * send a query to the backend and package up the result in a PGresult > * > * If the query was not even sent, return NULL; conn->errorMessage is set to > * a relevant message. > * If the query was sent, a new PGresult is returned (which could indicate > * either success or failure). > + * If it is called with metadata == 1, the metadata about the column > + * results will be obtained and saved in the PGresult. > * The user is responsible for freeing the PGresult via PQclear() > * when done with it. > */ > > ! static PGresult * > ! pqExec(PGconn *conn, const char *query, int metadata) > { > PGresult *result; > PGresult *lastResult; > bool savedblocking; > + int i; > > /* > * we assume anyone calling PQexec wants blocking behaviour, we force > *************** PQexec(PGconn *conn, const char *query) > *** 1363,1368 **** > --- 1443,1501 ---- > > if (PQsetnonblocking(conn, savedblocking) == -1) > return NULL; > + > + /* > + * If metadata is requested and everything is well, loop through > + * the result fields grabing the required information. > + */ > + > + if (metadata && (lastResult->numAttributes > 0)) > + for (i = 0; i < lastResult->numAttributes; i++) > + { > + Oid typenum; > + PGresult *result; > + char *tempname; > + static char query[] = "select typname from pg_type where oid = %lu"; > + char *fullquery; > + > + if ((typenum = lastResult->attDescs[i].typid) == 0) > + continue; > + > + /* Look up the cache for the type name. */ > + tempname = pqTypeCacheGet(conn, typenum); > + > + /* If it is a type that we still don't know the name, > + query for the type name and store it in the cache. */ > + if (tempname == NULL) > + { > + fullquery = malloc (sizeof(query) > + + 20 /* if Oids become 64 bits */); > + if (fullquery == NULL) > + { > + fprintf(stderr, "pqExec: malloc failed.\n"); > + return NULL; > + } > + /* If the typename was not in the cache, query the catalog > + and add it to the cache */ > + snprintf(fullquery, sizeof(query) + 20, query, typenum); > + result = PQexec(conn, fullquery); > + free(fullquery); > + if (!result || PQresultStatus(result) != PGRES_TUPLES_OK) > + { > + PQclear(result); > + continue; > + } > + if (PQntuples(result) != 1 || PQnfields(result) != 1) { > + PQclear(result); > + continue; > + } > + pqTypeCachePut(conn, typenum, PQgetvalue(result, 0, 0)); > + tempname = pqTypeCacheGet(conn, typenum); > + } > + > + lastResult->attDescs[i].atttypname = strdup(tempname); > + } > + > return lastResult; > > errout: > *************** PQftype(const PGresult *res, int field_n > *** 2104,2109 **** > --- 2237,2253 ---- > return InvalidOid; > } > > + char * > + PQftypeName(const PGresult *res, int field_num) > + { > + if (!check_field_number(res, field_num)) > + return NULL; > + if (res->attDescs) > + return res->attDescs[field_num].atttypname; > + else > + return NULL; > + } > + > int > PQfsize(const PGresult *res, int field_num) > { > *************** PQfmod(const PGresult *res, int field_nu > *** 2124,2129 **** > --- 2268,2330 ---- > return res->attDescs[field_num].atttypmod; > else > return 0; > + } > + > + int > + PQfprecision(const PGresult *res, int field_num) > + { > + int mod; > + char *type; > + > + if ((type = PQftypeName(res, field_num)) == NULL) > + return 0; > + mod = PQfmod(res, field_num); > + > + if (strcmp(type, "numeric") == 0) > + return ((0xFFFF0000) & mod) >> 16; > + else if (strcmp(type, "int2") == 0) > + return 5; > + else if (strcmp(type, "int4") == 0) > + return 10; > + else if (strcmp(type, "int8") == 0) > + return 19; /* It would be 20 if it was unsigned. */ > + else if (strcmp(type, "float4") == 0) > + return 6; > + else if (strcmp(type, "float8") == 0) > + return 15; > + else if (strcmp(type, "varchar") == 0 || > + strcmp(type, "bpchar") == 0 || > + strcmp(type, "char") == 0) > + return mod - 4; > + else if (strcmp(type, "varbit") == 0 || > + strcmp(type, "bit") == 0) > + return mod; > + > + return -1; > + } > + > + int > + PQfscale(const PGresult *res, int field_num) > + { > + int mod; > + char *type; > + > + if ((type = PQftypeName(res, field_num)) == NULL) > + return 0; > + mod = PQfmod(res, field_num); > + > + if (strcmp(type, "numeric") == 0) > + return ((0x0000FFFF) & mod) - 4; > + else if (strcmp(type, "int2") == 0 || > + strcmp(type, "int4") == 0 || > + strcmp(type, "int8") == 0) > + return 0; > + else if (strcmp(type, "float4") == 0) > + return -1; > + else if (strcmp(type, "float8") == 0) > + return -1; > + > + return -1; > } > > char * > Index: fe-misc.c > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-misc.c,v > retrieving revision 1.60 > diff -c -p -r1.60 fe-misc.c > *** fe-misc.c 2001/11/05 17:46:37 1.60 > --- fe-misc.c 2001/11/07 19:00:35 > *************** WSSE_GOODEXIT: > *** 896,898 **** > --- 896,974 ---- > } > > #endif > + > + char * > + PQinternal2common(const char *intname) > + { > + static char *typename; > + > + if (intname == NULL) > + return NULL; > + > + if (strcmp(intname, "int8") == 0) > + typename = "bigint"; > + else if (strcmp(intname, "bit") == 0) > + typename = "bit"; > + else if (strcmp(intname, "varbit") == 0) > + typename = "varbit"; /* bit varying */ > + else if (strcmp(intname, "bool") == 0) > + typename = "boolean"; > + else if (strcmp(intname, "box") == 0) > + typename = "box"; > + else if (strcmp(intname, "bpchar") == 0) > + typename = "char"; /* character */ > + else if (strcmp(intname, "varchar") == 0) > + typename = "varchar"; /* character varying */ > + else if (strcmp(intname, "cidr") == 0) > + typename = "cidr"; > + else if (strcmp(intname, "circle") == 0) > + typename = "circle"; > + else if (strcmp(intname, "date") == 0) > + typename = "date"; > + else if (strcmp(intname, "float8") == 0) > + typename = "double precision"; > + else if (strcmp(intname, "inet") == 0) > + typename = "inet"; > + else if (strcmp(intname, "int4") == 0) > + typename = "integer"; > + else if (strcmp(intname, "interval") == 0) > + typename = "interval"; > + else if (strcmp(intname, "line") == 0) > + typename = "line"; > + else if (strcmp(intname, "lseg") == 0) > + typename = "lseg"; > + else if (strcmp(intname, "macaddr") == 0) > + typename = "macaddr"; > + else if (strcmp(intname, "decimal") == 0) > + typename = "numeric"; > + else if (strcmp(intname, "numeric") == 0) > + typename = "numeric"; > + else if (strcmp(intname, "oid") == 0) > + typename = "oid"; > + else if (strcmp(intname, "path") == 0) > + typename = "path"; > + else if (strcmp(intname, "point") == 0) > + typename = "point"; > + else if (strcmp(intname, "polygon") == 0) > + typename = "polygon"; > + else if (strcmp(intname, "float4") == 0) > + typename = "real"; > + else if (strcmp(intname, "int2") == 0) > + typename = "smallint"; > + else if (strcmp(intname, "serial") == 0) > + typename = "serial"; > + else if (strcmp(intname, "text") == 0) > + typename = "text"; > + else if (strcmp(intname, "time") == 0) > + typename = "time"; > + else if (strcmp(intname, "time with time zone") == 0) > + typename = "time with time zone"; > + else if (strcmp(intname, "timestamp") == 0) > + typename = "timestamp"; > + else if (strcmp(intname, "timestamp with time zone") == 0) > + typename = "timestamp with time zone"; > + else > + typename = NULL; > + > + return typename; > + } > Index: libpq-fe.h > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-fe.h,v > retrieving revision 1.79 > diff -c -p -r1.79 libpq-fe.h > *** libpq-fe.h 2001/11/05 17:46:37 1.79 > --- libpq-fe.h 2001/11/07 19:00:35 > *************** extern "C" > *** 256,261 **** > --- 256,262 ---- > > /* Simple synchronous query */ > extern PGresult *PQexec(PGconn *conn, const char *query); > + extern PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > extern PGnotify *PQnotifies(PGconn *conn); > extern void PQfreeNotify(PGnotify *notify); > > *************** extern "C" > *** 303,315 **** > extern char *PQfname(const PGresult *res, int field_num); > extern int PQfnumber(const PGresult *res, const char *field_name); > extern Oid PQftype(const PGresult *res, int field_num); > extern int PQfsize(const PGresult *res, int field_num); > extern int PQfmod(const PGresult *res, int field_num); > extern char *PQcmdStatus(PGresult *res); > extern char *PQoidStatus(const PGresult *res); /* old and ugly */ > extern Oid PQoidValue(const PGresult *res); /* new and improved */ > ! extern char *PQcmdTuples(PGresult *res); > ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); > extern int PQgetlength(const PGresult *res, int tup_num, int field_num); > extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); > > --- 304,319 ---- > extern char *PQfname(const PGresult *res, int field_num); > extern int PQfnumber(const PGresult *res, const char *field_name); > extern Oid PQftype(const PGresult *res, int field_num); > + extern char *PQftypeName(const PGresult *res, int field_num); > extern int PQfsize(const PGresult *res, int field_num); > extern int PQfmod(const PGresult *res, int field_num); > + extern int PQfprecision(const PGresult *res, int field_num); > + extern int PQfscale(const PGresult *res, int field_num); > extern char *PQcmdStatus(PGresult *res); > extern char *PQoidStatus(const PGresult *res); /* old and ugly */ > extern Oid PQoidValue(const PGresult *res); /* new and improved */ > ! extern char *PQcmdTuples(PGresult *res); > ! extern char *PQgetvalue(const PGresult *res, int tup_num, int field_num); > extern int PQgetlength(const PGresult *res, int tup_num, int field_num); > extern int PQgetisnull(const PGresult *res, int tup_num, int field_num); > > *************** extern "C" > *** 371,376 **** > --- 375,383 ---- > /* Get encoding id from environment variable PGCLIENTENCODING */ > extern int PQenv2encoding(void); > > + /* Convert internal type name to common type name */ > + extern char *PQinternal2common(const char *intname); > + > #ifdef __cplusplus > } > #endif > Index: libpq-int.h > =================================================================== > RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/libpq-int.h,v > retrieving revision 1.44 > diff -c -p -r1.44 libpq-int.h > *** libpq-int.h 2001/11/05 17:46:38 1.44 > --- libpq-int.h 2001/11/07 19:00:35 > *************** union pgresult_data > *** 75,88 **** > char space[1]; /* dummy for accessing block as bytes */ > }; > > ! /* Data about a single attribute (column) of a query result */ > > typedef struct pgresAttDesc > { > ! char *name; /* type name */ > Oid typid; /* type id */ > int typlen; /* type size */ > int atttypmod; /* type-specific modifier info */ > } PGresAttDesc; > > /* Data for a single attribute of a single tuple */ > --- 75,91 ---- > char space[1]; /* dummy for accessing block as bytes */ > }; > > ! /* Data about a single attribute (column) of a query result. > ! * The type name is only available if PQexecIncludeMetadata() was used. > ! */ > > typedef struct pgresAttDesc > { > ! char *name; /* column name */ > Oid typid; /* type id */ > int typlen; /* type size */ > int atttypmod; /* type-specific modifier info */ > + char *atttypname; /* type name */ > } PGresAttDesc; > > /* Data for a single attribute of a single tuple */ > *************** typedef struct pgLobjfuncs > *** 191,196 **** > --- 194,208 ---- > Oid fn_lo_write; /* OID of backend function LOwrite */ > } PGlobjfuncs; > > + /* Entry in the cache of the correspondence between type Oids and type names. > + */ > + typedef struct pgTypeCache > + { > + Oid typenum; /* OID of type */ > + char *typename; /* name of type */ > + struct pgTypeCache *next; /* name of type */ > + } PGtypecache; > + > /* PGconn stores all the state data associated with a single connection > * to a backend. > */ > *************** struct pg_conn > *** 240,245 **** > --- 252,258 ---- > char cryptSalt[2]; /* password salt received from backend */ > PGlobjfuncs *lobjfuncs; /* private state for large-object access > * fns */ > + PGtypecache *typecache; /* cached types for this connection. */ > > /* Buffer for data received from backend and not yet processed */ > char *inBuffer; /* currently allocated buffer */ > *************** extern void pqSetResultError(PGresult *r > *** 305,310 **** > --- 318,324 ---- > extern void *pqResultAlloc(PGresult *res, size_t nBytes, bool isBinary); > extern char *pqResultStrdup(PGresult *res, const char *str); > extern void pqClearAsyncResult(PGconn *conn); > + extern void pqTypeCacheClear(PGconn *conn); > > /* === in fe-misc.c === */ > > > Index: libpq.sgml > =================================================================== > RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v > retrieving revision 1.72 > diff -c -p -r1.72 libpq.sgml > *** libpq.sgml 2001/09/13 15:55:23 1.72 > --- libpq.sgml 2001/11/07 19:06:52 > *************** PGresult *PQexec(PGconn *conn, > *** 728,733 **** > --- 728,748 ---- > <function>PQerrorMessage</function> to get more information about the error. > </para> > </listitem> > + > + <listitem> > + <para> > + <function>PQexecIncludeMetadata</function> > + Submit a query to the server and wait for the result; > + include extra metadata about the result fields. > + This makes available information such as the type name, > + precision and scale for each field in the result. > + <synopsis> > + PGresult *PQexecIncludeMetadata(PGconn *conn, > + const char *query); > + </synopsis> > + Used the same way as PQexec(). > + </para> > + </listitem> > </itemizedlist> > > <para> > *************** You can query the system table <literal> > *** 964,969 **** > --- 979,986 ---- > the name and properties of the various data types. The <acronym>OID</acronym>s > of the built-in data types are defined in <filename>src/include/catalog/pg_type.h</filename> > in the source tree. > + The function <function>PQftypename</function> can be used to retrieve the > + type name if the result was obtained via <function>PQexecIncludeMetadata</function>. > </para> > </listitem> > > *************** extracts data from a <acronym>BINARY</ac > *** 1010,1015 **** > --- 1027,1126 ---- > </para> > </listitem> > </itemizedlist> > + > + <para> > + The following functions only produce meaningful results if > + <function>PQexecIncludeMetadata</function> was used > + (as opposed to <function>PQexec</function>). > + </para> > + > + <itemizedlist> > + > + <listitem> > + <para> > + <function>PQftypename</function> > + Returns the name of the column type as a string. > + Field indices start at 0. > + <synopsis> > + char *PQftypename(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the name of the column type as a string. > + Copy the string if needed -- do not modify, free() > + or assume its persistence. The internal type name is > + returned; use PQtypeint2ext() to convert to a more SQL-ish style. > + NULL is returned if the field type name is not availble. > + </para> > + </listitem> > + > + <listitem> > + <para> > + <function>PQfprecision</function> > + Returns the precision of the field > + associated with the given field index. > + Field indices start at 0. > + <synopsis> > + int PQfprecision(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the precision of the field > + associated with the given field index. > + For numeric types (INTEGER, FLOAT, etc.), PQfprecision returns the > + number of decimal digits in the specified field. For character and bit > + string types, such as VARCHAR and BIT, PQfprecision returns the > + maximum number of characters/bits allowed in the specified field. > + PQfprecision returns 0 if precision information is not available and > + -1 if precision is not applicable to the field in question. The latter > + will be the case if the type of the field is POINT, for example. > + </para> > + </listitem> > + > + <listitem> > + <para> > + <function>PQfscale</function> > + Returns the scale of the field > + associated with the given field index. > + Field indices start at 0. > + <synopsis> > + int PQfscale(const PGresult *res, > + int field_index); > + </synopsis> > + Returns the scale of the field > + associated with the given field index. > + PQfscale returns the scale of the field associated with the given > + field index. Scale is the number of digits after the decimal point, > + so this function is useful only with fields that are of a numeric > + type (INTEGER, FLOAT, NUMERIC, etc.). -1 is returned if scale is not > + applicable to the field type. 0 is returned if scale information is > + not available. > + </para> > + </listitem> > + </itemizedlist> > + > + <para> > + Use the function below to convert internal type names (like the > + ones returned by <function>PQftypename</function>) into something > + more user-friendly. > + </para> > + > + <itemizedlist> > + <listitem> > + <para> > + <function>PQtypeint2ext</function> > + Converts an internal type name into a SQL-ish > + type name. > + <synopsis> > + char *PQtypeint2ext(const char **intname); > + </synopsis> > + Converts an internal type name into a SQL-ish > + type name. > + NULL is returned if the internal type is not recognized > + (which will be the case if the type is a UDT). > + </para> > + </listitem> > + > + </itemizedlist> > + > </sect2> > > <sect2 id="libpq-exec-select-values"> > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I have reviewed this patch and clearly has features I would like to get into 7.3. We have been pushing too much type knowledge into apps and this will give people a libpq solution that we can manage. Here are my comments. > > These seem okay, but I don't like the API detail that "0 is returned if > > information is not available". 0 is a valid result, at least for > > PQfscale. I would recommend returning -1. If you really want to > > distinguish bad parameters from non-numeric datatype, then return -1 > > and -2 for those two cases. > > > > This seems to be the libpq convention. On calls such as PQfsize and > PQfmod, for instance, zero is a valid result and is also returned if > the information is not available. > > Please note that we did not make this convention -- our original version > did return -1. But we decided that following a different rule for these > two routines was even more confusing. And change the return convention > for the whole set of functions at this point seems out of the question. > > P.S.: Maybe whoever originally designed the libpq interface was trying > to accomplish some sort of "soft fail" by returning zero. Just a guess > of course. I think the problem stems from the fact that some of our functions legitimately can return -1, so zero was chosen as a failure code, while others use -1 for failure. In fact, Tom mentioned that there are now some types that have a valid atttypmod of 0 (timestamp?) meaning we may have a problem there anyway. Any ideas on how to fix it? In hindsight, we should have defined a macro equal to -2 and report that as the failure return for all functions that need it. > > > Most programs won't need this information and may not be willing > > > to pay the overhead for metadata retrieval. Thus, we added > > > an alternative function to be used instead of PQexec if one > > > wishes extra metadata to be retrieved along with the query > > > results: > > > > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > > > This strikes me as very ugly, and unnecessary, and inefficient since > > it retrieves metadata for all columns even though the client might > > only need to know about some of them. > > This part I would not worry about. The new routines are for result sets > (not arbitrary columns) so the fields present in it have already been > pre-selected. Also, this kind of information is useful for tools as > they don't know beforehand what the fields will be. In all cases > we can think of, the tool will always want metadata about all the > fields. I hesitate to add another PQexec function. That could complicate the API. > > An even worse problem is that > > it'll fail entirely with a multi-query query string. > > > > This is a bummer. But I see no solution for this besides documenting > the restriction in the manual. If I am not mistaken we already have > the limitation of returning just the last result anyway (we just > collect the error messages). > > > > What I think would be cleaner would be to do the metadata queries > > on-the-fly as needed. With the caching that you already have in there, > > on-the-fly queries wouldn't be any less efficient. > > > > But to do a metadata query we must have access to the connection. > > We could handle it two ways: > > > > 1. Add a PGconn parameter to the querying functions. > > > > The problem is that results may be kept longer than connections > (see below). The current solution did not require the connection > as the metadata is for the result set, not tables. > > The PGconn parameter would be reasonable for retrieving metadata > about table columns, for instance. I think this is the way to go. We just require the connection be valid. If it isn't, we throw an error. I don't see this as a major restriction. In fact, it would be interesting to just call this function automatically when someone requests type info. > > 2. Make use of the PGconn link that's stored in PGresults, and > > specify that these functions can only be used on PGresults that > > came from a still-open connection. > > > > That field has been deprecated (see comments in the source code) > because a result may be kept even after the connection is closed. > > > > I think I prefer the first, since it makes it more visible to the > > programmer that queries may get executed. But it's a judgment call > > probably; I could see an argument for the second as well. Any comments, > > anyone? > > > > It would have to be the former (to avoid the stale pointer problem). > > But requiring a connection adds a restriction to the use of this info > and makes it have a different life span than the object it refers to > (a PGresult), which is very weird. Yes, but how often is this going to happen? If we can throw a reliable error message when it happens, it seems quite safe. "If you are going to get type info, keep the connection open so we can get it." > > > The PQftypename function returns the internal PostgreSQL type name. > > > As some programs may prefer something more user friendly than the > > > internal type names, we've thrown in a conversion routine as well: > > > char *PQtypeint2ext(const char *intname); > > > This routine converts from the internal type name to a more user > > > friendly type name convention. > > > > This seems poorly designed. Pass it the type OID and typmod, both of > > which are readily available from a PQresult without extra computation. > > That will let you call the backend's format_type ... of course you'll > > need a PGconn too for that. > > > > Requiring the PGconn is bad. But we still could have a PQFtypeExt() > returning the "external" type if people prefer it that way. > We thought that this should be kept as an explicit conversion > operation to make clear the distinction of what the backend knows > about and this outside world view of things. If they want more info about the result set, keeping the connection open so we can get that information seems perfectly logical. If we put it in the manual in its own section as MetaData functions, and mention they need a valid connection to work, I think it will be clear to everyone. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > I have reviewed this patch and clearly has features I would like to get > into 7.3. We have been pushing too much type knowledge into apps and > this will give people a libpq solution that we can manage. Here are my > comments. > We definitively want this to go into 7.3. I am planning on update this patch next week. > > > These seem okay, but I don't like the API detail that "0 is returned if > > > information is not available". 0 is a valid result, at least for > > > PQfscale. I would recommend returning -1. If you really want to > > > distinguish bad parameters from non-numeric datatype, then return -1 > > > and -2 for those two cases. > > > > > > > This seems to be the libpq convention. On calls such as PQfsize and > > PQfmod, for instance, zero is a valid result and is also returned if > > the information is not available. > > > > Please note that we did not make this convention -- our original version > > did return -1. But we decided that following a different rule for these > > two routines was even more confusing. And change the return convention > > for the whole set of functions at this point seems out of the question. > > > > P.S.: Maybe whoever originally designed the libpq interface was trying > > to accomplish some sort of "soft fail" by returning zero. Just a guess > > of course. > > I think the problem stems from the fact that some of our functions > legitimately can return -1, so zero was chosen as a failure code, while > others use -1 for failure. In fact, Tom mentioned that there are now > some types that have a valid atttypmod of 0 (timestamp?) meaning we may > have a problem there anyway. Any ideas on how to fix it? > We have agreed to change the error return code to -2. It will be in the REPOST of the patch next week. > In hindsight, we should have defined a macro equal to -2 and report that > as the failure return for all functions that need it. > Note that -2 is a valid result for some other functions :-( There is no way of picking a value that works for all. Maybe these functions should just be returning a value and setting some global 'libpqerr' variable that had to be set to assure the result was valid. Anyway, it is too late for that now as backwards compatibility makes it difficult to change the API that much. > > > > Most programs won't need this information and may not be willing > > > > to pay the overhead for metadata retrieval. Thus, we added > > > > an alternative function to be used instead of PQexec if one > > > > wishes extra metadata to be retrieved along with the query > > > > results: > > > > > > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > > > > > This strikes me as very ugly, and unnecessary, and inefficient since > > > it retrieves metadata for all columns even though the client might > > > only need to know about some of them. > > > > This part I would not worry about. The new routines are for result sets > > (not arbitrary columns) so the fields present in it have already been > > pre-selected. Also, this kind of information is useful for tools as > > they don't know beforehand what the fields will be. In all cases > > we can think of, the tool will always want metadata about all the > > fields. > > I hesitate to add another PQexec function. That could complicate the > API. > We have agreed to add another call to set a flag for including the metadata on the PQexec call (which would make it work like the PQexecIncludeMetadata described above). It will be in the REPOST patch. Question: should it affect only the next PQexec(), or should we require the user to reset it? How do we call it? I am thinking of PQsetIncludeMetadata() Name suggestions for this call are welcome. > > > An even worse problem is that > > > it'll fail entirely with a multi-query query string. > > > > > > > This is a bummer. But I see no solution for this besides documenting > > the restriction in the manual. If I am not mistaken we already have > > the limitation of returning just the last result anyway (we just > > collect the error messages). > > > > > > > What I think would be cleaner would be to do the metadata queries > > > on-the-fly as needed. With the caching that you already have in there, > > > on-the-fly queries wouldn't be any less efficient. > > > > > > But to do a metadata query we must have access to the connection. > > > We could handle it two ways: > > > > > > 1. Add a PGconn parameter to the querying functions. > > > > > > > The problem is that results may be kept longer than connections > > (see below). The current solution did not require the connection > > as the metadata is for the result set, not tables. > > > > The PGconn parameter would be reasonable for retrieving metadata > > about table columns, for instance. > > I think this is the way to go. We just require the connection be valid. > If it isn't, we throw an error. I don't see this as a major restriction. > In fact, it would be interesting to just call this function > automatically when someone requests type info. > Sorry but we disagree on this one. The metadata is related (part of) a result, which is a different object, with a different life spam. There is no way to be certain that the connection is still around and no reliable way of testing for it. If the conn field is a dangling pointer any check based on it depends on that heap memory not being realocated already. Well, we could keep a list of results associated with a connection and go cleaning the conn pointers in it _if_ the user uses PQfinish() properly. A little bit dangerous IMO. I would stick with Tom Lane's decision of deprecating pconn and leave the metadata independent of it. > > > 2. Make use of the PGconn link that's stored in PGresults, and > > > specify that these functions can only be used on PGresults that > > > came from a still-open connection. > > > > > > > That field has been deprecated (see comments in the source code) > > because a result may be kept even after the connection is closed. > > > > > > > I think I prefer the first, since it makes it more visible to the > > > programmer that queries may get executed. But it's a judgment call > > > probably; I could see an argument for the second as well. Any comments, > > > anyone? > > > > > > > It would have to be the former (to avoid the stale pointer problem). > > > > But requiring a connection adds a restriction to the use of this info > > and makes it have a different life span than the object it refers to > > (a PGresult), which is very weird. > > Yes, but how often is this going to happen? If we can throw a reliable > error message when it happens, it seems quite safe. "If you are going to > get type info, keep the connection open so we can get it." > There is no reliable way of detecting this error (see above). > > > > The PQftypename function returns the internal PostgreSQL type name. > > > > As some programs may prefer something more user friendly than the > > > > internal type names, we've thrown in a conversion routine as well: > > > > char *PQtypeint2ext(const char *intname); > > > > This routine converts from the internal type name to a more user > > > > friendly type name convention. > > > > > > This seems poorly designed. Pass it the type OID and typmod, both of > > > which are readily available from a PQresult without extra computation. > > > That will let you call the backend's format_type ... of course you'll > > > need a PGconn too for that. > > > > > > > Requiring the PGconn is bad. But we still could have a PQFtypeExt() > > returning the "external" type if people prefer it that way. > > We thought that this should be kept as an explicit conversion > > operation to make clear the distinction of what the backend knows > > about and this outside world view of things. > > If they want more info about the result set, keeping the connection open > so we can get that information seems perfectly logical. If we put it in > the manual in its own section as MetaData functions, and mention they > need a valid connection to work, I think it will be clear to everyone. > This may be possible for this specific conversion routine. The advantage is that we don't need to keep this translation in the clients (so we don't have to track changes etc). I will take a look into this possibility. We would have conn as a parameter for this call though (will not use the dangling pointer inside the result). -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser <fnasser@redhat.com> writes: > We have agreed to add another call to set a flag for including the > metadata on the PQexec call (which would make it work like the > PQexecIncludeMetadata described above). It will be in the REPOST patch. That works for me. Among other things, it solves the problem where the code that wants the metadata is a layer or two above the place that's actually issuing PQexec. Setting a persistent option in the PGconn object gets around the difficulty that the caller of PQexec may not know metadata will be wanted later. > Question: should it affect only the next PQexec(), or should we require > the user to reset it? It should be persistent till reset, see above. > An even worse problem is that > it'll fail entirely with a multi-query query string. I'm still quite unhappy about this; it more or less destroys the layer independence mentioned above. Please think harder. Perhaps it could be set up so that metadata is only collected for the last result of a query string, after you determine that there are no more results? Which is still not great, but better than failing outright with multi-query strings. regards, tom lane
Fernando Nasser wrote: > Bruce Momjian wrote: > > > > I have reviewed this patch and clearly has features I would like to get > > into 7.3. We have been pushing too much type knowledge into apps and > > this will give people a libpq solution that we can manage. Here are my > > comments. > > > > We definitively want this to go into 7.3. I am planning on update > this patch next week. Great. > > I hesitate to add another PQexec function. That could complicate the > > API. > > > > We have agreed to add another call to set a flag for including the > metadata on the PQexec call (which would make it work like the > PQexecIncludeMetadata described above). It will be in the REPOST patch. > > Question: should it affect only the next PQexec(), or should we require > the user to reset it? > > How do we call it? I am thinking of PQsetIncludeMetadata() > Name suggestions for this call are welcome. Uh, is it more efficient to do the setting before the query is called? If so, I guess is would remain active until you turn off off. That seems the clearest. I like the separate function to turn it on. > > > The PGconn parameter would be reasonable for retrieving metadata > > > about table columns, for instance. > > > > I think this is the way to go. We just require the connection be valid. > > If it isn't, we throw an error. I don't see this as a major restriction. > > In fact, it would be interesting to just call this function > > automatically when someone requests type info. > > > > Sorry but we disagree on this one. The metadata is related (part of) > a result, which is a different object, with a different life spam. > There is no way to be certain that the connection is still around > and no reliable way of testing for it. If the conn field is a > dangling pointer any check based on it depends on that heap memory > not being realocated already. Well, we could keep a list of results > associated with a connection and go cleaning the conn pointers in it > _if_ the user uses PQfinish() properly. A little bit dangerous IMO. > > I would stick with Tom Lane's decision of deprecating pconn and leave > the metadata independent of it. Oh, no reliable way to determine the error; that is bad. Does your new PQsetIncludeMetadata() eliminate the need for the connection pointer? If so, it is clearly better than the connection parameter as you suggest. I guess I am getting confused. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
BTW, I also had a bunch of concerns having to do with odd-seeming choices about what information would be wired into libpq and what would be retrieved at runtime from the backend. I don't recall the details at the moment, but I want to raise a flag that that is still an issue for me. I'd like to see some explicit design decisions about what information will be treated in which way. regards, tom lane
Tom Lane wrote: > BTW, I also had a bunch of concerns having to do with odd-seeming > choices about what information would be wired into libpq and what > would be retrieved at runtime from the backend. I don't recall the > details at the moment, but I want to raise a flag that that is still > an issue for me. I'd like to see some explicit design decisions > about what information will be treated in which way. I noticed that too, and looked into it. I didn't see any hard-wired oids (at least that I remember), but I did see cases where the scale/precision results had to be accessed based on the specific type involved, e.g. NUMERIC. I don't see a way around this, and in fact most people are doing this type-speicific stuff in their apps. The only other solution I can see is adding a backend function that does these type-specific manipulations and returns them to the client. This seems quite attractive, especially considering changes in internal type representations between releases. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Tom Lane wrote: > > Fernando Nasser <fnasser@redhat.com> writes: > > We have agreed to add another call to set a flag for including the > > metadata on the PQexec call (which would make it work like the > > PQexecIncludeMetadata described above). It will be in the REPOST patch. > > That works for me. Among other things, it solves the problem where the > code that wants the metadata is a layer or two above the place that's > actually issuing PQexec. Setting a persistent option in the PGconn > object gets around the difficulty that the caller of PQexec may not know > metadata will be wanted later. > > > Question: should it affect only the next PQexec(), or should we require > > the user to reset it? > > It should be persistent till reset, see above. > Agreed. > > An even worse problem is that > > it'll fail entirely with a multi-query query string. > > I'm still quite unhappy about this; it more or less destroys the layer > independence mentioned above. Please think harder. Perhaps it could > be set up so that metadata is only collected for the last result of a > query string, after you determine that there are no more results? > Which is still not great, but better than failing outright with > multi-query strings. > I will look into doing like you suggest. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Should this capability be added some day? --------------------------------------------------------------------------- Fernando Nasser wrote: > > Tom Lane wrote: > > > > Fernando Nasser <fnasser@cygnus.com> writes: > > > This is a patch that was posted some time ago to pgsql-patches and > > > no one has commented on it. > > > It adds something that JDBC has that is not present in libpq (see > below). > > > Is it OK for inclusion? > > > > Here are some comments ... > > > > Thanks. > > > > int PQfprecision(const PGresult *res, int field_num); > > > int PQfscale(const PGresult *res, int field_num); > > > > > Return Scale and Precision of the type respectively. > > > > These seem okay, but I don't like the API detail that "0 is returned if > > information is not available". 0 is a valid result, at least for > > PQfscale. I would recommend returning -1. If you really want to > > distinguish bad parameters from non-numeric datatype, then return -1 > > and -2 for those two cases. > > > > This seems to be the libpq convention. On calls such as PQfsize and > PQfmod, for instance, zero is a valid result and is also returned if > the information is not available. > > Please note that we did not make this convention -- our original version > did return -1. But we decided that following a different rule for these > two routines was even more confusing. And change the return convention > for the whole set of functions at this point seems out of the question. > > P.S.: Maybe whoever originally designed the libpq interface was trying > to accomplish some sort of "soft fail" by returning zero. Just a guess > of course. > > > > > Most programs won't need this information and may not be willing > > > to pay the overhead for metadata retrieval. Thus, we added > > > an alternative function to be used instead of PQexec if one > > > wishes extra metadata to be retrieved along with the query > > > results: > > > > > PGresult *PQexecIncludeMetadata(PGconn *conn, const char *query); > > > > This strikes me as very ugly, and unnecessary, and inefficient since > > it retrieves metadata for all columns even though the client might > > only need to know about some of them. > > This part I would not worry about. The new routines are for result sets > (not arbitrary columns) so the fields present in it have already been > pre-selected. Also, this kind of information is useful for tools as > they don't know beforehand what the fields will be. In all cases > we can think of, the tool will always want metadata about all the > fields. > > > > An even worse problem is that > > it'll fail entirely with a multi-query query string. > > > > This is a bummer. But I see no solution for this besides documenting > the restriction in the manual. If I am not mistaken we already have > the limitation of returning just the last result anyway (we just > collect the error messages). > > > > What I think would be cleaner would be to do the metadata queries > > on-the-fly as needed. With the caching that you already have in there, > > on-the-fly queries wouldn't be any less efficient. > > > > But to do a metadata query we must have access to the connection. > > We could handle it two ways: > > > > 1. Add a PGconn parameter to the querying functions. > > > > The problem is that results may be kept longer than connections > (see below). The current solution did not require the connection > as the metadata is for the result set, not tables. > > The PGconn parameter would be reasonable for retrieving metadata > about table columns, for instance. > > > > 2. Make use of the PGconn link that's stored in PGresults, and > > specify that these functions can only be used on PGresults that > > came from a still-open connection. > > > > That field has been deprecated (see comments in the source code) > because a result may be kept even after the connection is closed. > > > > I think I prefer the first, since it makes it more visible to the > > programmer that queries may get executed. But it's a judgment call > > probably; I could see an argument for the second as well. Any comments, > > anyone? > > > > It would have to be the former (to avoid the stale pointer problem). > > But requiring a connection adds a restriction to the use of this info > and makes it have a different life span than the object it refers to > (a PGresult), which is very weird. > > > > > The PQftypename function returns the internal PostgreSQL type name. > > > As some programs may prefer something more user friendly than the > > > internal type names, we've thrown in a conversion routine as well: > > > char *PQtypeint2ext(const char *intname); > > > This routine converts from the internal type name to a more user > > > friendly type name convention. > > > > This seems poorly designed. Pass it the type OID and typmod, both of > > which are readily available from a PQresult without extra computation. > > That will let you call the backend's format_type ... of course you'll > > need a PGconn too for that. > > > > Requiring the PGconn is bad. But we still could have a PQFtypeExt() > returning the "external" type if people prefer it that way. > We thought that this should be kept as an explicit conversion > operation to make clear the distinction of what the backend knows > about and this outside world view of things. > > > > -- > Fernando Nasser > Red Hat Canada Ltd. E-Mail: fnasser@redhat.com > 2323 Yonge Street, Suite #300 > Toronto, Ontario M4P 2C9 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073