Fetching latest commit…
Cannot retrieve the latest commit at this time.
|Failed to load latest commit information.|
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.