On Sun, Apr 4, 2010 at 7:11 PM, Adam Thompson <athom...@athompso.net> wrote: >> Also all math within the function should be integer math, and the >> return >> result should be left as full precision real. >> This will avoid all rounding error. >> The result can then be rounded at application level. > > It's impossible (AFAIK) to avoid all rounding errors in monetary amounts > as long as there continue to exist - anywhere in the world - irrational > denominations.
Well, technically they are still rational. We haven't yet seen 1/pi denominations or the like ;-) > I can't name any such systems *currently* in use, although the most > obvious example is the Commonwealth (British) Shilling and Pence, set at > 1s=12d. (Abbr for pence is d, not p.) If you want a fully generalizable > monetary currency system, you probably also have to accommodate such > bizzarities as the conversion between shillings and sheep :-) (1 shilling > = 1 sheep in Anglo-Saxon era, FYI... who needs a gold standard?!) FWIW, > the old French sou was worth 12 deniers (origin of "d" as abbreviation). Hence we should probably stick with the tradeoff I was mentioning and say "decimal only." > > If you want to retain rational mathematics, never mind integer math, > you'll pretty much have to declare that the type explicitly supports only > currencies in use from 1971-Feb-15 onward, although that's probably better > phrased as: > * You should probably limit the type to decimalized currencies, and > state > that limitation explicitly That's probably good enough, given the background of this subproject (see below). > > Aside from historical curiosities, the more I read of this proposal, the > more I smell feeping creaturism... increasingly, it doesn't sound like > something that can reasonably be black-boxed into a custom data type; > arbitrary monetary declaration, registration, etc., is an entire > application's worth of data, or at least a major subsystem. I would > compare this to the TZ subsystem in unix-like OSes: the complexity quickly > becomes insane once you start capturing temporal data. As an example, > what do you do with currencies that have been rebased or devalued, yet > retain their ISO currency code? The only solution I can see is to also > require an "effective date" as part of the type definition. If I want to > compare two pieces of data denominated in the *same* currency (ignoring my > previous case), since we have to store historical data (at least about > exchange rates, anyway) does the comparison take into account the > difference in a single currency's valuation? Ok, so here is some additional background (not really relevant to the implementation but may be of help in understanding why this proposal, why now, why officially a separate project). A couple weeks ago a discussion on the Pgsql-bugs list surfaced over currency handling and the PostgreSQL money type. The money type is one of those strange beasts which is "deprecated pending enhancements" (IOW not fully functional, best if you don't use it, but it will be enhanced and further supported at some point) and after the discussion went on a while it became clear that what a lot of people needed was not simply an enhancement to what the money type does today but a very good multicurrency type. At least one other individual with a very different set of applications needed has decided he needs something similar. So what I have been collaborating on is a two-part enhancement for PostgreSQL: Part 1 is a multicurrency monetary set of data types. These include basic operations (monetary + monetary, monetary + numeric, monetary/monetary, monetary/numeric, and so forth). There are a few other functions to convert to numeric, to extract a currency, and to represent as text. A function to change between currencies at a specified rate will be part of this module. While I am involved in the spec process I will probably not be writing this section (which will probably be written in C). Part 2 is a monetary subsystem for financial applications. This will include currency validation logic, date-based exchange-rate handling, and the like. Other collaborators maintain other PostgreSQL-backed projects which have multicurrency requirements. Based on this, my guess is that this will be a separate Pg-foundry project licensed under the BSD license. Two of the major reasons for trying to make this generally applicable include: 1) Increasing collaboration with other projects (perhaps with luck getting the data type/operator support included in PostgreSQL as a contrib module eventually) and thus offloading it from the LedgerSMB project, and 2) Ensuring that other projects' requirements help show us where we may be missing something. Hope this helps, 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
