Re: Proposal for 2.0: New monetary data types

[Alejandro Imass <..hidden..>]

On Fri, Apr 2, 2010 at 2:06 PM, Chris Travers <..hidden..> wrote:
> 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:
>

Awesome ideas. I have thought of taking this a step further and
instead of having a base currency in a particular coin, a
multi-currency system could have a high precision "base value unit".
It's much like having a base coin, but in generic value units that
have constant value. That is, your base VU will not fluctuate due to
exchange rates and such. I know this dwelves into some complex
economic theory but since you are evaluating ideas for multi-currency
there is my .02.

Best,
Alejandro Imass

[...]