Re: [PATCHES] Fixes for MONEY type using locale

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.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(1row)
 

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

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +