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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general