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 ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Ledger-smb-devel mailing list Ledger-smb-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
