Thread: pg_typeof equivalent for numeric scale, numeric/timestamp precision?

Hi all

While examining a reported issue with the JDBC driver I'm finding myself
wanting SQL-level functions to get the scale and precision of a numeric
result from an operation like:

   select NUMERIC(8,4) '1.9999'
   union
   select INTEGER 4;

I can write:

   SELECT pg_typeof(a), a FROM (
     select NUMERIC(8,4) '1.9999'
     union
     select 4::integer
   ) x(a);

but I didn' t see any SQL-level way to get the scale and precision. The
output of `pg_typeof` is a `regtype` so it doesn't have any given scale
and precision, it's just the raw type. I didn't find any functions with
"scale" or "precision" in their name, nor any functions matching
*numeric* that looked promising. *typmod* only found in- and out-
functions. Nothing matching *type* looked good.

There's `format_type`, but it requires you to supply the typomod, it
can't get it from a result for you. Worse, it doesn't seem to offer a
way to set scale, only precision, so it's of limited utility for numeric
anyway, since every numeric it produces is invalid ("numeric precision
must be between 1 and 1000").

Will I need to do this from C with a custom function, or via libpq's
metadata APIs? And re format_type, am I misunderstanding it or is it
just busted for numeric?

--
Craig Ringer

On Tue, Oct 02, 2012 at 10:19:18AM +0800, Craig Ringer wrote:
> Hi all
>
> While examining a reported issue with the JDBC driver I'm finding
> myself wanting SQL-level functions to get the scale and precision of
> a numeric result from an operation like:
>
>   select NUMERIC(8,4) '1.9999'
>   union
>   select INTEGER 4;

The typmod in postgres is not maintained very well. In the
wire-protocol the typmod is provided if known (the C interface calls
the function PQfmod) and if it's not there (which is very often,
postgres doesn't try very hard) there's no way to "encourge" postgres
to work it out for you.

As for no SQL level functions, you could probably write a function to
determine the scale/precision of a given *value*, but not for a whole
column.  But once you have to string representation of the value you
have that anyway...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

On 10/03/2012 05:50 AM, Martijn van Oosterhout wrote:
> On Tue, Oct 02, 2012 at 10:19:18AM +0800, Craig Ringer wrote:
>> Hi all
>>
>> While examining a reported issue with the JDBC driver I'm finding
>> myself wanting SQL-level functions to get the scale and precision of
>> a numeric result from an operation like:
>>
>>    select NUMERIC(8,4) '1.9999'
>>    union
>>    select INTEGER 4;
>
> The typmod in postgres is not maintained very well. In the
> wire-protocol the typmod is provided if known (the C interface calls
> the function PQfmod) and if it's not there (which is very often,
> postgres doesn't try very hard) there's no way to "encourge" postgres
> to work it out for you.

OK, thanks for confirming that.

It seems the JDBC spec requires preservation of type qualifiers in ways
that PgJDBC just won't be able to do without that info. If the server
doesn't keep track of it I don't think there's anything PgJDBC can do to
be compliant on that topic :-(

--
Craig Ringer