Re: Libpq support for precision and scale

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 ...

> 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