Type scale returned by PQfmod() 65531 for time/timestamp output parameter?

From: Sebastien FLAESCH <sf(at)4js(dot)com>
To: pgsql-interfaces(at)lists(dot)postgresql(dot)org
Subject: Type scale returned by PQfmod() 65531 for time/timestamp output parameter?
Date: 2018-02-15 15:03:22
Message-ID: 093e1214-7057-f80c-5c3c-74076b7bb555@4js.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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

Responses

Browse pgsql-interfaces by date

  From Date Subject
Next Message Tom Lane 2018-02-15 16:25:40 Re: Type scale returned by PQfmod() 65531 for time/timestamp output parameter?
Previous Message Ashutosh Sharma 2018-01-12 15:06:01 Re: Compatibility modes supported in ECPG