Currency conversion and aggregation in PostgreSQL database

In our application we store multiple project fundings of different donors that they insert in their respective currency, e.g. EUR for Germany, SEK for Sweden etc.

The idea is to provide reports for these projects in one currency.. We are currently storing the amount of funding together with it's currency in a table like this:

development=> \d funding_forecasts
                                   Table "public.funding_forecasts"
   Column    |          Type          |                           Modifiers                            
-------------+------------------------+----------------------------------------------------------------
 id          | integer                | not null default nextval('funding_forecasts_id_seq'::regclass)
 project_id  | integer                | 
 year        | integer                | 
 currency    | character varying(255) | 
 payments    | integer         开发者_开发技巧       | 
 commitments | integer                | 

We are further having an exchange_rates table that stores exchange rates from a base currency to a specific currency for each year.

Now the problem is that when we are creating reports, we need to query a lot of funding tables for each project and are thus having a huuuuge amount of queries + numerous currency conversions and aggregations on the application side.

What I think would be cool because Rails isn't very flexible with custom SQL (I know it can be but it is a hassle) is to do the conversion more or less transparently, maybe using a custom data type that allows easy aggregation (using sum and +/- etc. operators).. I'm not sure about this at all anyway so all comments are very much appreciated!

---

Have you seen the tagged types stuff by Martijn van Oosterhout?

http://svana.org/kleptog/pgsql/taggedtypes.html

---

Not having worked with currency conversion much before, it seems like a lot over overhead to make exchange rates over time and perform all the calculations.

It would seem to me that a better solution might be to store the money in a single currency, based upon the day that money is phsyically converted into your currency. So if Donor A sends a check in SEK, but your organization works in EUR, you take the check to the bank, they exchange it to EUR. I would enter the amount the bank gives me in EUR.

I know this solution doesn't work well for commitments for future donations. Is it possible to ask everyone to donate in the same currency?

---

I think you should add another column for your table - payments_in_usd. When donor pays then save the paid amount in donor's currency and also in USD based on exchange rate of time of payment. For future commitments you don't know what will be the amount in USD but you can easily just use current exchange rate for current value of commitment.

To check what you expect to be payed in USD then just use:

select sum(commitments*current_exchange_rate(currency))

When you want to know what how much was payed then just:

select sum(payments_in_usd)