NOTE 1: Do not use the MONEY type in the first place as it is deprecated!
NOTE 2: These MONEY casting routines are provided as a convenience for
those wishing to convert tables that use the MONEY type to a better data
type like FLOAT8 or NUMERIC(16,2), for example. Also, because these
routines are written in C, they may help provide a smooth transition
from the MONEY data type to another type with only a small (or
negligible) performance hit.
NOTE 3: These routines will not convert directly from MONEY to NUMERIC,
but you can accomplish the same goal by casting to FLOAT8 first. Here
is a contrived example:
template1> select 3.14::money::float8::numeric(16,2);
Not sure if anyone found these MONEY conversion routines useful, but I
found a bug in them. If you tried to cast a NULL value of type MONEY,
an error would be produced (instead of returning NULL, which is the
correct thing to do).
Apply the attached patch in the contrib/money/ directory after first
applying the initial patch:
# patch -p0 < money.diff
N.B. There must be some black magic going on in the bowels of
PostgreSQL-7.0.x for the int2 and int4 routines, as they return values
instead of pointers to values, but the database correctly guesses between
a NULL return value and a 0 (zero) return value, which are identical as
far as a C compiler is concerned. Guess that's one reason for the change
to the 'Datum' return type in PostgreSQL-7.1.x!
Dave
On Thu, Jun 14, 2001 at 09:31:06AM -0500, David D. Kilzer wrote:
> Attached please find a patch to cast (convert) the MONEY type to any one
> of the following types:
>
> INT2, INT4, INT8, FLOAT4, FLOAT8
>
> The patch is against PostgreSQL v7.0.3 and creates files in a new
> directory named contrib/money/. I'm pretty sure it won't compile
> against v7.1, but it won't be too hard to fix. (I'll do this when I
> upgrade to v7.1 at work, which will hopefully happen sooner rather than
> later.)
>
> I wrote this because I needed to convert MONEY to a standard decimal
> value and didn't want to do it on the client in Perl, and I didn't want
> to use a plpgsql function that used string parsing. Both solutions were
> too inefficient for my needs.
>
> http://postgresql.bteg.net/mhonarc/pgsql-general/2000-01/msg00446.html
>
> I wouldn't have used the MONEY type in the first place if I had known it
> was deprecated, but I didn't see this notice in the v7.0.3 documentation
> two to three months ago when designing the database. Oh well, my loss
> is your gain. :^)
>
> I've attempted to set the Reply-To field to pgsql-hackers, but please
> copy me on any posts as well.
>
> Comments welcome!
>
> Dave