Re: [GENERAL] A real currency type - Mailing list pgsql-hackers

On Tue, Mar 21, 2006 at 08:49:18AM -0500, Bruce Momjian wrote:
>
> This looks very interesting.  Should we add it to the core distribution?

Excellent question. As yet I have received very little feedback on it,
though it does work as advertised. I have had some people complain that
while they'd like to use it (especially the timestamp-that-remembers-
the-timezone), they don't like the idea of an external module.

I suppose the are a few technical issues that could be raised, like the
fact that it searches user tables during the parse phase, but this is a
generic problem with non-immutable type input functions.

I'd like it to be considered for inclusion. If the interest is there I
can make any changes people suggest.

Have a nice day,

>
> ---------------------------------------------------------------------------
>
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > For a while I've been wondering about making a type that was really a
> > shell around a base type that tagged the type in some way. For example,
> > associating a currency with a numeric and complaining about additions
> > between mismatches.
> >
> > Well, I did it and it's available here:
> > http://svana.org/kleptog/pgsql/taggedtypes.html
> >
> > Below some examples of it in action. Yes, that's a timestamp that
> > remembers the timezone. Neat huh?
> >
> > Tested on 7.4 and a recent 8.1devel so it should work for most people.
> > Installation reports welcome. Note, this is beta software, don't run it
> > on your production server. Thanks.
> >
> > Have a nice day,
> >
> >
> > test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
> >  ?column?
> > -----------
> >  10.10 USD
> > (1 row)
> >
> > test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> > ERROR:  Using operator +(currency,currency) with incompatable tags (USD,AUD)
> > test=# select c1, print_currency(c1) from c;
> >      c1     | print_currency
> > ------------+----------------
> >  232.44 USD | US$    232.44
> >  21.20 EUR  | ?       21.20
> >  -13.44 AUD | AU$    -13.44
> >  0.01 USD   | US$      0.01
> >  14.00 AUD  | AU$     14.00
> > (5 rows)
> >
> > test=# select 5.4*c1 from c where tag(c1) = 'AUD';
> >   ?column?
> > ------------
> >  -72.58 AUD
> >  75.60 AUD
> > (2 rows)
> >
> > test=# select t, "timestamp"(t), date_part('hour',t) from c;
> >                     t                    |      timestamp      | date_part
> > -----------------------------------------+---------------------+-----------
> >  2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 |         2
> >  2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |        10
> >  2005-08-14 02:00:00+02 Asia/Hong_Kong   | 2005-08-14 08:00:00 |         8
> >  2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |        20
> >  2005-08-14 02:00:00+02 Asia/Kuwait      | 2005-08-14 03:00:00 |         3
> > (5 rows)
> >
> > --
> > Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> -- End of PGP section, PGP failed!
>
> --
>   Bruce Momjian   http://candle.pha.pa.us
>   SRA OSS, Inc.   http://www.sraoss.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.