agora inbox for pgsql-interfaces@postgresql.org  

help / color / mirror / Atom feed

From: Sebastien FLAESCH <sf@4js.com>
To: pgsql-interfaces@postgresql.org
Subject: Type identification with libpq / PQFmod() when using aggregates (SUM)
Date: Mon, 14 Apr 2014 12:06:39 +0200
Message-ID: <534BB32F.4020503@4js.com> (raw)
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (3+ messages)  latest in thread

Message-ID: <534BB32F.4020503@4js.com>
Permalink:  ../534BB32F.4020503@4js.com/
Also on:    postgresql.org/message-id/534BB32F.4020503@4js.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-interfaces@postgresql.org
  Cc: sf@4js.com
  Subject: Re: Type identification with libpq / PQFmod() when using aggregates (SUM)
  In-Reply-To: <534BB32F.4020503@4js.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox