Re: dividing money by money - Mailing list pgsql-bugs

On Fri, Apr 2, 2010 at 9:51 AM, Dimitri Fontaine <dfontaine@hi-media.com> w=
rote:

>> One could also then store monetary[] arrays for addressing specific
>> denomination storage. =A0I.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. =A0We could also store things like the cash
>> counted from a till at the end of the day by denomination. =A0One 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.

Sure.  See below.
>
>> Anyway, that's my basic thinking. =A0One 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=85

Well, you have another problem (this may be wandering far afield from
the original question but here it goes):

Suppose I live in Canada and I have two checking accounts for my
business, one in CAD and one in USD.  In essence I have to account for
a floating balance of a foreign currency.  Consequently, I don't think
you can just suggest "convert at insert time" as a way to handle that.
In cases where you do (payments converted on deposit), that's a subset
of the more general problem, which is converting at an arbitrary point
in time.  However, even where you do (suppose instead I live in the US
and someone pays me in CAD), there is no guarantee that the conversion
rate when you enter the payment into your system as received and when
you convert it is the same.  The check could be deposited the next
day, for example and converted at that point.  OTOH, if it is an
incoming wire transfer in AUD, I would expect it to be converted on
receipt.

So conversion between currencies is something which has to be done at
a specified point in time.  While some of this could be automated to
an extent, it would really be business-specific.

In essence, I think you would need a function like
convert_currency(source monetary, target curr, date) to do the
conversion.  Furthermore this would require currency tables, and would
be probably outside the core data type definition.

In essence, to handle exchange rates, I think you would need
additional tables and the like, and UDF's to do the actual
conversions.  For simplicity's sake, I think this would be broken off
into a separate module although I would be happy to collaborate on
that as well.