Re: WIP: to_char, support for EEEE format - Mailing list pgsql-hackers

2009/8/3 Tom Lane <tgl@sss.pgh.pa.us>:
> Euler Taveira de Oliveira <euler@timbira.com> writes:
>> As I said in a prior e-mail, Oracle has a diferent overflow limit (-84 to 127).
>> In PostgreSQL, the numeric datatype can have up to 1000 digits (ie 1e+999) and
>> the double precision datatype can have up to 309 digits (ie 1e-307 or 1e+308).
>> We should support up to 3 exponent digits so all of our native datatypes are
>> covered by the to_char() function.
>
> Uh, no, we had better support more.  The actual limit of the current
> numeric format is 1e+131072.
>
> As long as we consider that EEEE should emit as many exponent digits
> as needed, this isn't particularly critical.  But it would be if we
> try to specify an exact number of output digits.
>

Well, I tried this and as it turns out the patch casts the value to a
float8 in order to pass it on to snprintf for sci-notation formatting.See the following chunk in numeric_to_char():
else if (IS_EEEE(&Num)){    float8 val;
    val = DatumGetFloat8(DirectFunctionCall1(numeric_float8,
NumericGetDatum(value)));
    numstr = orgnum = (char *) palloc(MAXDOUBLEWIDTH + 1);    len = snprintf(orgnum, MAXDOUBLEWIDTH + 1, "%.*e",
Num.post,val);} 

This leads to the following behaviour:

=# select to_char(1e+1000::numeric, '9.99EEEE');
ERROR:
"10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"
is out of range for type double precision

If we want to support the full range of numeric values with EEEE, I
guess we would need to write our own implementation of scientific
notation rather than depend on sprintf()'s?

Cheers,
BJ