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

2009/7/29 Brendan Jurd <direvus@gmail.com>:
> 2009/7/29 Euler Taveira de Oliveira <euler@timbira.com>:
>> This is not a problem with your patch but something that needs to be fixed in
>> PostgreSQL to match Oracle behavior. The following example should emit an
>> error. IMHO, filling the string with # is very strange. TODO?
>>
>> euler=# SELECT to_char(1234.56789, '9.080');
>>  to_char
>> ---------
>>  #.#8#
>> (1 row)
>
> The formatting functions have a lot of weird corner cases.  I've been
> trying to clean up some of the more bizarre behaviours in the
> date/time formatting functions, but haven't touched the numeric
> formatting because I haven't ever needed to use it.
>
> Filling unused characters in the string with "#" may be strange, but
> changing it would require a much broader patch that covers all of the
> numeric formatting styles, not just EEEE.  A TODO is probably the way
> to go.
>
>>
>> Couldn't the following code be put inside switch clause? If not, you should
>> add a comment why the validation is outside switch.
>>
>> +   if (IS_EEEE(num) && n->key->id != NUM_E)
>> +   {
>> +       NUM_cache_remove(last_NUMCacheEntry);
>> +       ereport(ERROR,
>> +               (errcode(ERRCODE_SYNTAX_ERROR),
>> +                errmsg("\"EEEE\" must be the last pattern used")));
>> +   }
>> +
>>    switch (n->key->id)
>>    {
>>        case NUM_9:
>
> The switch is on (n->key->id), but the test you mentioned above is
> looking for any keywords *other than* the EEEE keyword, where EEEE has
> previously been parsed.
>
> So if you put the test inside the switch, it would need to appear in
> every single branch of the switch except for the NUM_E one.  I'm
> confused about why you think this needs a comment.  Perhaps I
> misunderstood you?
>
>>
>> Oracle has a diferent overflow limit [1] but I think we could stay with the
>> PostgreSQL one. But the #.#### is not the intended behavior. IIRC you're
>> limited to 99 exponent.
>>
>> SQL> SELECT to_char(1.234567E+308, '9.999EEEE');
>> SELECT to_char(1.234567E+308, '9.999EEEE')
>>
>> ERROR at line 1:
>> ORA-01426: numeric overflow
>>
>> euler=# SELECT to_char(1.234567E+308, '9.999EEEE');
>>  to_char
>> -----------
>>  #.#######
>> (1 row)
>
> I don't see any problem with extending this to allow up to 3 exponent
> digits ... Pavel, any comment?
>

I am not sure - this function should be used in reports witl fixed
line's width. And I am thinking, so it's should be problem - I prefer
showing some #.### chars. It's clean signal, so some is wrong, but it
doesn't break generating long run reports (like exception in Oracle)
and doesn't broke formating like 3 exponent digits.

Pavel

>>
>> The problem is in numeric_to_char() and float8_to_char(). You could fix it
>> with the following code. Besides that I think you should comment why '5' or
>> '6' in the other *_to_char() functions.
>>
>> +       /* 6 means '.' (decimal point), 'E', '+', and 3 exponent digits */
>
> Agreed about the comment; I'll add it.
>
> Cheers,
> BJ
>