README

README for currency contrib module
----------------------------------

This postgres module implements an arbitrary precision, multi-currency
money type.

* support for up to 999 currencies in a currency lookup table

* monetary values stored in the same amount of space as a NUMERIC,
  plus 2 bytes.

* convenient conversion between currencies

* addition of values in different currencies; nomination of a
  "neutral" currency via auxilliary table

* fast bulk comparisons (eg sorting a large result set by price), by
  internally caching exchange rates table


Defined Entities
----------------

TLA is defined as a basic type, represented as a 15-bit quantity
internally (stored in an int2)

CURRENCY is also defined as a basic type, which wraps the "numeric"
type and associates a TLA with it; the currency code.

CURRENCY_RATE is a defined lookup table; entries must be inserted into
it before any values can be constructed.

This contains:

  currency_code: a three-letter currency code

  description: an arbitrary text comment for the code

  symbol: prefix for display purposes (may be NULL, in which case the
          code is used as a suffix instead)

  minor: for currencies which use a minor currency unit, the number of
         decimal points between the major and the minor currency unit.
         eg 2 for USD, EUR, 0 for currencies which don't use a minor
         currency unit.  For divisible, high unit value currencies
         like gold etc, use 5 or higher.

  rate: conversion rate to the neutral currency

  is_exchange: boolean, set to 't' for the neutral currency - there
               MUST be exactly ONE neutral currency.


Supported Operations
--------------------

The examples below assume that CURRENCY_RATE is pre-loaded with EUR
and NZD and that EUR has a symbol defined, but NZD doesn't.

Conversion to/from text returns any 'useless' precision present:

    '100EUR'::currency        =  '100 EUR'
    '-100EUR'::currency       = '-100 EUR'
    '+100.000EUR'::currency   =  '100.000 EUR'

Printing using relevant currency sign and precision:

    format('100EUR'::currency) =   '€ 100.00'
    #'100EUR'::currency        =   '€ 100.00'
    #'100NZD'::currency        =   'NZD 100.00'

Get components out:

    code('100EUR'::currency)  = 'EUR'::tla
    value('100EUR'::currency)  = '100'::numeric

Valid ways to combine a currency code and a number:

    currency(100, 'eur')       = '100 EUR'
    (100::text || 'EUR')::currency

Conversion to a particular currency:

    '100EUR'::currency->'USD' = '132.40 USD'::currency

Conversion to the 'exchange' or 'neutral' currency, you can simply
cast as 'money';

    '100EUR'::currency::money = '$600.00'::money

Otherwise, you have to use the 'particular currency' mechanism above.

Note that the 'money' type is limited to a 64-bit quantity of whatever
the smallest unit of currency is.

Comparisons between currency values are supported; they need not be of
the same currency type.

    '100EUR'::currency > '100GBP'::currency
    ... ORDER BY my_currency_column;

Addition of units (assuming BTC as the 'exchange' currency, exchanging
at 4 BTC = 1 USD is used a neutral currency):

    '100EUR'::currency + '10USD'::currency = '5997.19 BTC'::currency

Other math on currency units:

    '100EUR'::currency - '10EUR'::currency =  '90 EUR'::currency
    '100EUR'::currency / 7     =  '14.28 EUR'::currency
    '14.28EUR'::currency * 7   =  '99.96 EUR'::currency
    '100EUR'::currency / 7 * 7 = '100.00 EUR'::currency (see below)

    '100EUR'::currency / '10EUR' = 10::numeric

    '100EUR'::currency + 10                => type error
    '100EUR'::currency * '10EUR'::currency => type error

    -'100EUR'::currency        = '-100 EUR'::currency
    +'100EUR'::currency        = '100 EUR'::currency


Indexing
--------

To support queries which might join by or sort by currency values,
there are operator classes defined.

However, do not index currency values; the functions which back them
are not IMMUTABLE, so if you create indexes with them then those
indexes may not be able to retrieve your data or otherwise behave
bizarrely.  Postgres should notice this and refuse to create the
index, but doesn't currently, so just beware.


Rounding
--------

All decisions on precision and suchlike are punted on and given to the
NUMERIC type underlying the values.  To avoid lots of useless
precision, use the format() functions.


Copyright and License
---------------------
This contrib/ module is Copyright (c) 2010, 2011, Adioso Ltd.  This
module is free software; you may use it under the same terms as
Postgres itself.