Proposal for 2.0: New monetary data types

[Chris Travers <..hidden..>]

Hi all;

I have been having some discussion on the PostgreSQL lists about
spinning some of this off to a Pg-foundry project.  However, one of
the big issues which is difficult in 1.3 and needs to be solved for
2.0 is the issue of multi-currency balances.  I would like to create a
multi-currency data type for PostgreSQL which can be currency aware
and address certain things we can't address today in terms of currency
conversions.

The base type would look something like this:

CREATE DOMAIN curr varchar(3);
CREATE TYPE monetary AS (amount numeric, denomination numeric, currency curr);
CREATE FUNCTION numeric(monetary) returns numeric as $$ select
$1.amount * $1.denomination; $$ language sql;
CREATE CAST (monetary AS numeric) with function numeric(monetary);

An exchange rate conversion function would also be supplied though
perhaps not through this Pg-foundry module.  Syntax would look
something like:

SELECT convert_currency(monetary(1, 1, 'USD'), 'CAD', now()::date);

I would also like to see payments handled differently, so that one
could mark the payment as converted at a specific point in time.  The
further approach would be to allow us to properly track floating
balances in foreign currencies.  There are two major use cases here:

1)  I receive a check from a customer in CAD.  I record it.  It goes
into the bank the next day.  The time to properly exchange that
currency is the day of deposit, not the day of receipt.  The exchange
rates can vary slightly....

2)  Far more importantly, it would allow us to better handle floating
balances of foreign currencies, for example in bank accounts of
currencies other than the default currency for the system.  FX gains
and losses could be calculated for balance sheet purposes on an ad-hoc
basis for such outstanding floating balances and could be recorded at
date of conversion.....

Finally, including a denomination attribute would allow better
tracking of till closings than we currently have.

Of course an additional module (also perhaps to be put on Pg-foundry)
would track valid currencies, exchange rates, and the like.

In general, what do folks think?

Best Wishes,
Chris Travers