On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:
> Jeremy Lowery <jslowery@xxxxxxxxx> writes:
> > I load and dump text files with currency values in it. The decimal in these
> > input and output formats in implied. The V format character works great for
> > outputing numeric data:
>
> > # select to_char(123.45, '999V99');
> > to_char
> > ---------
> > 12345
> > (1 row)
>
> > However, when importing data, the V doesn't do the same thing:
>
> > # select to_number('12345', '999V99');
>
> A look at the source code shows that to_number doesn't do anything at
> all with the V format code, so this isn't terribly surprising. It
> wouldn't be very hard to make it do the right thing, probably, but
> nobody's had that particular itch yet. Feel free to scratch it and
> send a patch ...
(This is for 9.6.)
I have developed the attached patch to support 'V' with to_number().
Oracle doesn't support that, so we are on our own in defining the API.
The patch doesn't handle non-whole-number strings very well as there is
no way for the user to specify decimal precision because we have
overridden the decimal digit meaning, but that seems fine to me as most
users will be using whole numbers.
--
Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index b1e94d7..b895757
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 6090,6101 ****
<listitem>
<para>
! <literal>V</literal> effectively
multiplies the input values by
<literal>10^<replaceable>n</replaceable></literal>, where
<replaceable>n</replaceable> is the number of digits following
! <literal>V</literal>.
! <function>to_char</function> does not support the use of
<literal>V</literal> combined with a decimal point
(e.g., <literal>99.9V99</literal> is not allowed).
</para>
--- 6090,6103 ----
<listitem>
<para>
! <literal>V</literal> with <function>to_char</function>
multiplies the input values by
<literal>10^<replaceable>n</replaceable></literal>, where
<replaceable>n</replaceable> is the number of digits following
! <literal>V</literal>. <literal>V</literal> with
! <function>to_number</function> divides in a similar manner.
! <function>to_char</function> and <function>to_number</function>
! do not support the use of
<literal>V</literal> combined with a decimal point
(e.g., <literal>99.9V99</literal> is not allowed).
</para>
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 84e4db8..f615af3
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*************** numeric_to_number(PG_FUNCTION_ARGS)
*** 5047,5053 ****
VARSIZE(value) - VARHDRSZ, 0, 0, false, PG_GET_COLLATION());
scale = Num.post;
! precision = Max(0, Num.pre) + scale;
if (shouldFree)
pfree(format);
--- 5047,5053 ----
VARSIZE(value) - VARHDRSZ, 0, 0, false, PG_GET_COLLATION());
scale = Num.post;
! precision = Num.pre + Num.multi + scale;
if (shouldFree)
pfree(format);
*************** numeric_to_number(PG_FUNCTION_ARGS)
*** 5056,5061 ****
--- 5056,5078 ----
CStringGetDatum(numstr),
ObjectIdGetDatum(InvalidOid),
Int32GetDatum(((precision << 16) | scale) + VARHDRSZ));
+
+ if (IS_MULTI(&Num))
+ {
+ Numeric x;
+ Numeric a = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
+ Int32GetDatum(10)));
+ Numeric b = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
+ Int32GetDatum(-Num.multi)));
+
+ x = DatumGetNumeric(DirectFunctionCall2(numeric_power,
+ NumericGetDatum(a),
+ NumericGetDatum(b)));
+ result = DirectFunctionCall2(numeric_mul,
+ result,
+ NumericGetDatum(x));
+ }
+
pfree(numstr);
return result;
}
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
