Re: [PATCHES] Fixes for MONEY type using locale

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Well if we are going to continue to support money (which I am against)
> > we should support the casting to numeric as that is by far a more
> > common implementation of money and we will have mixed environments.
>
> So, you don't use MONEY, and you don't want to, but nonetheless you
> know better than the people who do use MONEY what they need.

I found out you can cast numerics to MONEY by using two casts:

test=> SELECT 12321.12::text::money;
money
------------
$12,321.12
(1 row)

For some reason this doesn't work in 8.2 but does in 8.3. The reverse
doesn't work:

test=> SELECT '12321.12'::money::text::numeric;
ERROR: invalid input syntax for type numeric: "$12,321.12"

The big problem is that MONEY is a string so the dollar sign and commas
are a problem.

> Aside from the semantic-gap issue, there is the point that providing
> a cast might actually mask application errors. I can well imagine
> cases where one of the reasons for using MONEY is *exactly* that it's
> not a plain number or easily convertible to one.

Perhaps all we need is a way to accomplish the casting so it isn't
automatic. This works:

test=> SELECT regexp_replace('2343'::money::text, '[^$,]*', '', 'g')::numeric;
regexp_replace
----------------
2343.00
(1 row)

but the '$' and ',' are locale-specific and I can't think of a way to do
this generically.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +