Chris Travers <ch...@metatrontech.com> writes: > Ok. Here is my application: I write a multi-currency accounting > program backed by PostgreSQL. After 1.3 is released (2Q this year), > we expect to be doing a full redesign. > > What I am thinking about is having a custom data type, something like:
I'd be interested into participating into such a project, in particular a multi-currencies support type coded in C would be useful for me. > CREATE DOMAIN curr VARCHAR(3); > CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier > NUMERIC); This reduces into two basic components: a value (amount * > multiplier) and a currency identifier (USD, etc). > > One could also then store monetary[] arrays for addressing specific > denomination storage. I.e. "When closing the till we had 26 pennies, > 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 > $20 bills." > > Then we can allow NUMERIC arithmetic on monetary amounts provided that > the CURR field is the same. We could also store things like the cash > counted from a till at the end of the day by denomination. One could > have easy monetary::numeric casts as well. Sounds a good starting point, but it sounds like we'll have to think about it to see how it survive a more detailed approach. > Anyway, that's my basic thinking. One could further add currency > conversion tables to an application if necessary. That's where it become interesting. Finding a nice way to solve the problem of more than one currency in the same table, with dated (timestamped?) conversion rates that are possibly unknown at INSERT timeā¦ -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs