Re: Libpq support for precision and scale

Should this capability be added some day?

---------------------------------------------------------------------------

Fernando Nasser wrote:
>
> Tom Lane wrote:
> >
> > Fernando Nasser <fnasser(at)cygnus(dot)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(at)redhat(dot)com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario M4P 2C9
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073