To: pgsql-interfaces@lists.postgresql.org
From: Sebastien FLAESCH <sf@4js.com>
Subject: Type scale returned by PQfmod() 65531 for time/timestamp output
 parameter?
Organization: Four Js Development Tools
Message-ID: <093e1214-7057-f80c-5c3c-74076b7bb555@4js.com>
Date: Thu, 15 Feb 2018 16:03:22 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi all,

(PostgreSQL 10.beta2)

I am using the PQfmod() function to get type information of result set columns.

When using a stored function returning output parameters defined with as time[(n)]
or timestamp[(n)], PQfmod() always returns the same SCALE (65531/0xFFFB), no matter
what time/timestamp precision is used.

With a regular SELECT on the same table columns I get other scale values, that allow
me to distinguish the time/timestamp fraction precision:

       2/0x0002  for time/timestamp(6)
       1/0x0001  for time/timestamp(5)
       0/0x0000  for time/timestamp(4)
   65535/0xFFFF  for time/timestamp(3)
   65534/0xFFFE  for time/timestamp(2)
   65533/0xFFFD  for time/timestamp(1)
   65532/0xFFFC  for time/timestamp(0)
   etc

I get the scale as follows:

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

Is this correct?
I Could not find detailed documentation about the interpretation of PQfmod()...

Here the SQL code of the stored function:

create function proc240(
              in  p_pkey    integer,
              out p_dt_y2i  timestamp without time zone,
              out p_dt_y2s  timestamp without time zone,
              out p_dt_y2f2 timestamp(2) without time zone,
              out p_dt_y2f3 timestamp(3) without time zone,
              out p_dt_y2f5 timestamp(5) without time zone,
              out p_dt_h2i  time without time zone,
              out p_dt_h2s  time without time zone,
              out p_dt_h2f2 time(2) without time zone,
              out p_dt_h2f3 time(3) without time zone,
              out p_dt_h2f5 time(5) without time zone
         )
as $$
begin
   select
     dt_y2i,
     dt_y2s,
     dt_y2f2,
     dt_y2f3,
     dt_y2f5,
     dt_h2i,
     dt_h2s,
     dt_h2f2,
     dt_h2f3,
     dt_h2f5
   into
     p_dt_y2i,
     p_dt_y2s,
     p_dt_y2f2,
     p_dt_y2f3,
     p_dt_y2f5,
     p_dt_h2i,
     p_dt_h2s,
     p_dt_h2f2,
     p_dt_h2f3,
     p_dt_h2f5
   from t240 where pkey = p_pkey;
end;
$$ language plpgsql


Then I am doing this:

   select * from proc240(101)

To get the output parameters.


Thanks for you help!
Seb