Skip site navigation (1) Skip section navigation (2)

Re: WIP: to_char, support for EEEE format

From: Euler Taveira de Oliveira <euler(at)timbira(dot)com>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP: to_char, support for EEEE format
Date: 2009-07-28 21:08:15
Message-ID: 4A6F68BF.4040902@timbira.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Brendan Jurd escreveu:
> Please find attached version 4 of the patch, and incremental diff from
> version 3.  It fixes the "eeee" bug ("eeee" is now accepted as a valid
> form of "EEEE"), and lifts the restriction on only having one digit
> before the decimal point.
> 
Looks better but I did some tests and caught some strange behaviors.

SQL> SELECT to_char(1234.56789, '8.999EEEE') FROM DUAL;
SELECT to_char(1234.56789, '8.999EEEE') FROM DUAL

ERROR at line 1:
ORA-01481: invalid number format model


SQL> SELECT to_char(1234.56789, '9.080EEEE') FROM DUAL;
SELECT to_char(1234.56789, '9.080EEEE') FROM DUAL

ERROR at line 1:
ORA-01481: invalid number format model

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)

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:

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)

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 */
+       if (isnan(value) || is_infinite(value) ||
+			len > Num.pre + Num.post + 6)
+       {
+           numstr = (char *) palloc(Num.pre + Num.post + 7);
+           fill_str(numstr, '#', Num.pre + Num.post + 6);
+           *(numstr + Num.pre) = '.';
+       }

I can't see more problems in your patch. When you fix it, it'll be ready for a
committer.


[1]
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref80


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/

In response to

Responses

pgsql-hackers by date

Next:From: Dean RasheedDate: 2009-07-28 21:10:54
Subject: Re: Deferred uniqueness versus foreign keys
Previous:From: Pavel StehuleDate: 2009-07-28 20:53:08
Subject: plpgsql: support identif%TYPE[], (from ToDo)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group