Thread: [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
Re: [PATCH] Contrib C source for casting MONEY to INT[248] and FLOAT[48]
From
"David D. Kilzer"
Date:
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