[PATCH] Contrib C source for casting MONEY to INT[248] and FLOAT[48]

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

"David D. Kilzer" <ddkilzer(at)lubricants-oil(dot)com> writes:
> Attached please find a patch to cast (convert) the MONEY type to any one
> of the following types:
> INT2, INT4, INT8, FLOAT4, FLOAT8

Hmm. It strikes me as a rather bad idea to introduce implicit casting
of MONEY to all these other types --- I suspect that's going to create
a bunch of parsing ambiguities.

> 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.

Seems like a better answer is to use NUMERIC instead of MONEY in the
first place...

regards, tom lane

On Tue, Jun 19, 2001 at 10:20:18AM -0400, Tom Lane wrote:

> "David D. Kilzer" <ddkilzer(at)lubricants-oil(dot)com> writes:
> > Attached please find a patch to cast (convert) the MONEY type to any one
> > of the following types:
> > INT2, INT4, INT8, FLOAT4, FLOAT8
>
> Hmm. It strikes me as a rather bad idea to introduce implicit casting
> of MONEY to all these other types --- I suspect that's going to create
> a bunch of parsing ambiguities.

Well, that's why the patch puts the code under contrib/. I meant this
to be optional code that could be added to PostgreSQL if people needed
the functionality, i.e., for transition work. I'm sorry if that wasn't
clear in my original post.

Should I have not submitted this code to the pgsql-patches list?

> > 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.
>
> Seems like a better answer is to use NUMERIC instead of MONEY in the
> first place...

The reason for this (in my case) was covered in the next paragraph:

> > 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.

So for those of us who accidentally used the MONEY type (or had legacy
tables that used the MONEY type), these functions (especially the
FLOAT8 type conversion) are extremely useful for transition work.

I don't really care if the code is included with future PostgreSQL
releases or not. I just wanted to save other people some time if or
when they decide to transition off the MONEY type.

Dave

Tom outlined the problems with this patch. We want to remove Money and
have it be part of Numeric.

> 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

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

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