Thread: [Fwd: [PATCHES] Libpq support for precision and scale]

[Fwd: [PATCHES] Libpq support for precision and scale]

From

Fernando Nasser

Date:

26 November 2001, 17:24:28

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

Re: Libpq support for precision and scale

From

Tom Lane

Date:

27 November 2001, 15:33:46

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

Re: Libpq support for precision and scale

From

Fernando Nasser

Date:

28 November 2001, 13:32:47

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

Re: Libpq support for precision and scale

From

Tom Lane

Date:

28 November 2001, 14:24:49

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

Re: [Fwd: [PATCHES] Libpq support for precision and scale]

From

Bruce Momjian

Date:

22 February 2002, 21:29:37

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

Re: [Fwd: [PATCHES] Libpq support for precision and scale]

From

Bruce Momjian

Date:

22 February 2002, 21:35:33

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

Re: [Fwd: [PATCHES] Libpq support for precision and scale]

From

Bruce Momjian

Date:

04 March 2002, 17:59:33

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

Re: Libpq support for precision and scale

From

Bruce Momjian

Date:

07 March 2002, 02:11:27

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

Re: Libpq support for precision and scale

From

Fernando Nasser

Date:

07 March 2002, 23:43:16

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

Re: Libpq support for precision and scale

From

Tom Lane

Date:

07 March 2002, 23:47:18

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

Re: Libpq support for precision and scale

From

Bruce Momjian

Date:

07 March 2002, 23:47:18

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

Re: Libpq support for precision and scale

From

Tom Lane

Date:

07 March 2002, 23:54:51

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

Re: Libpq support for precision and scale

From

Bruce Momjian

Date:

07 March 2002, 23:57:58

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

Re: Libpq support for precision and scale

From

Fernando Nasser

Date:

08 March 2002, 00:04:16

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

Re: Libpq support for precision and scale

From

Bruce Momjian

Date:

25 August 2002, 19:29:29

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