Re: WIP: to_char, support for EEEE format

2009/7/29 Euler Taveira de Oliveira <euler(at)timbira(dot)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?

>
> 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