pg_typeof equivalent for numeric scale, numeric/timestamp precision? - Mailing list pgsql-general

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