Fixes issue 14769: to_text function now supports scientific notation
authorAugusto Mauch <augusto.mauch@openbravo.com>
Mon, 13 Feb 2012 16:30:57 +0100
changeset 15481 34727a4bec62
parent 15480 e4e3a55d6378
child 15482 d573ede8052e
Fixes issue 14769: to_text function now supports scientific notation
src-db/database/model/prescript-PostgreSql.sql
--- a/src-db/database/model/prescript-PostgreSql.sql	Mon Feb 13 16:00:40 2012 +0100
+++ b/src-db/database/model/prescript-PostgreSql.sql	Mon Feb 13 16:30:57 2012 +0100
@@ -166,10 +166,24 @@
 text
 )
 RETURNS NUMERIC AS '
+DECLARE
+  v_Pos INTEGER;
+  v_Mant NUMERIC;
+  v_Exp NUMERIC;
+  v_Res NUMERIC;
 BEGIN
-RETURN to_number($1, ''S99999999999999D9999999999'');
-EXCEPTION 
-  WHEN OTHERS THEN 
+  v_Pos := position(''E'' in upper($1));
+  IF v_Pos = 0 THEN
+    -- this is the old behaviour
+    RETURN to_number($1, ''S99999999999999D999999'');
+  ELSE
+    v_Mant := substring($1 from 1 for v_Pos - 1); -- Mantissa, implicit cast to data type NUMERIC
+    v_Exp := substring($1 from v_Pos + 1); -- Exponent, implicit cast to data type NUMERIC
+    v_Res := v_Mant * power(10, v_Exp);
+    RETURN v_Res;
+  END IF;
+EXCEPTION
+  WHEN OTHERS THEN
     RETURN NULL;
 END;
 ' LANGUAGE 'plpgsql' IMMUTABLE