agora inbox for pgsql-interfaces@postgresql.org
help / color / mirror / Atom feedType identification with libpq / PQFmod() when using aggregates (SUM)
3+ messages / 2 participants
[nested] [flat]
* Type identification with libpq / PQFmod() when using aggregates (SUM)
@ 2014-04-14 10:06 Sebastien FLAESCH <sf@4js.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sebastien FLAESCH @ 2014-04-14 10:06 UTC (permalink / raw)
To: pgsql-interfaces
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
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Type identification with libpq / PQFmod() when using aggregates (SUM)
@ 2014-04-14 14:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sebastien FLAESCH <sf@4js.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2014-04-14 14:01 UTC (permalink / raw)
To: Sebastien FLAESCH <sf@4js.com>; +Cc: pgsql-interfaces
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
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Type identification with libpq / PQFmod() when using aggregates (SUM)
@ 2014-04-14 14:24 Sebastien FLAESCH <sf@4js.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Sebastien FLAESCH @ 2014-04-14 14:24 UTC (permalink / raw)
To: pgsql-interfaces
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
--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-04-14 14:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-14 10:06 Type identification with libpq / PQFmod() when using aggregates (SUM) Sebastien FLAESCH <sf@4js.com>
2014-04-14 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-14 14:24 ` Sebastien FLAESCH <sf@4js.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox