Re: Libpq support for precision and scale

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