Обсуждение: Type identification with libpq / PQFmod() when using aggregates (SUM)

Hello,

In a libpq client application, we need to properly identify the data type
when fetching data produced from a SELECT, therefore we use the PQftype()
and PQfmod() APIs ...

Consider a column defined with the following data type:
   interval hour to second(0)

When executing a SELECT query using directly the columns values (no aggregate),
the libpq APIs (PQftype and PQfmod) return clear type information.

With an interval hour to second(0), we get:
   PQftype() = 1186   PQfmode() = 469762048 (pgprec=7168, pgscal=65532, pgleng=469762044)

where precision, scale and length are computed as follows:

#define VARHDRSZ 4    int pgfmod = PQfmod(st->pgResult, i);    int pgprec = (pgfmod >> 16);    int pgscal = ((pgfmod -
VARHDRSZ)& 0xffff);    int pgleng = (pgfmod - VARHDRSZ);
 

But when using an aggregate function like SUM(), PQfmode() function returns
"no information available" (-1) ...

Is the type of the result of an aggregate function (or even more complex
expressions) not known by the server?

Is this considered as bug or is it expected?

I found not much information in the PQfmod() description.

A workaround is to cast the result of the aggregate function:
   SELECT CAST( SUM(mycol) AS INTERVAL HOUR TO SECOND(0) ) FROM ...

But I just wonder that the type of the result is not just the same as
the type of the source column...

Thanks!
Seb

Sebastien FLAESCH <sf@4js.com> writes:
> But when using an aggregate function like SUM(), PQfmode() function returns
> "no information available" (-1) ...

> Is the type of the result of an aggregate function (or even more complex
> expressions) not known by the server?

That's correct.
        regards, tom lane

On 04/14/2014 04:01 PM, Tom Lane wrote:
> Sebastien FLAESCH <sf@4js.com> writes:
>> But when using an aggregate function like SUM(), PQfmode() function returns
>> "no information available" (-1) ...
>
>> Is the type of the result of an aggregate function (or even more complex
>> expressions) not known by the server?
>
> That's correct.

Thank you Tom, but then, IMHO, it should be considered as a request...

Some client applications (which do a bit more than just displaying the result
string of query columns) need that information, for ex to do data conversions,
automatic formatting, etc.

Seb