Module org.postgresql.pljava
Package org.postgresql.pljava.adt

Interface Money<T>

All Superinterfaces:
Adapter.Contract<T>, Adapter.Contract.Scalar<T>
Functional Interface:
This is a functional interface and can therefore be used as the assignment target for a lambda expression or method reference.
@FunctionalInterface public interface Money<T> extends Adapter.Contract.Scalar<T>
The MONEY type's PostgreSQL semantics: an integer value, whose scaling, display format, and currency are all determined by a user-settable configuration setting.

This type is a strange duck in PostgreSQL. It is stored as a (64 bit) integer, and must have a scaling applied on input and output to the appropriate number of decimal places.

The appropriate scaling, the symbols for decimal point and grouping separators, how the sign is shown, and even what currency it represents and the currency symbol to use, are all determined from the locale specified by the lc_monetary configuration setting, which can be changed within any session with no special privilege at any time. That may make MONEY the only data type in PostgreSQL where a person can use a single SET command to instantly change what an entire table of data means.

For example, this little catalog of products: 

 => SELECT * FROM products;
  product |       price
 ---------+--------------------
  widget  |             $19.00
  tokamak | $19,000,000,000.00

can be instantly marked down by about 12 percent (at the exchange rates looked up at this writing): 

 => SET lc_monetary TO 'ja_JP';
 SET
 => SELECT * FROM products;
  product |        price
 ---------+---------------------
  widget  |             ￥1,900
  tokamak | ￥1,900,000,000,000

or marked up by roughly the same amount: 

 => SET lc_monetary TO 'de_DE@euro';
 SET
 => SELECT * FROM products;
  product |        price
 ---------+---------------------
  widget  |             19,00 €
  tokamak | 19.000.000.000,00 €

or marked up even further (as of this writing, 26%): 

 => SET lc_monetary TO 'en_GB';
 SET
 => SELECT * FROM products;
  product |       price
 ---------+--------------------
  widget  |             £19.00
  tokamak | £19,000,000,000.00

Obtaining the locale information in Java

Before the integer value provided here can be correctly scaled or interpreted, the locale-dependent information must be obtained. In Java, that can be done in six steps:

  1. Obtain the string value of PostgreSQL's lc_monetary configuration setting.
  2. Let's not talk about step 2 just yet.
  3. Obtain a Locale object by passing the BCP 47 tag to Locale.forLanguageTag.
  4. Pass the Locale object to NumberFormat.getCurrencyInstance.
  5. From that, obtain an actual instance of Currency with NumberFormat.getCurrency.
  6. Obtain the correct power of ten for scaling from Currency.getDefaultFractionDigits.

The NumberFormat obtained in step 4 knows all the appropriate formatting details, but will not automatically scale the integer value here by the proper power of ten. That must be done explicitly, and to avoid compromising the precision objectives of the MONEY type, should be done with something like a BigDecimal. If fmt was obtained in step 4 above and scale is the value from step 6: 

 BigDecimal bd =
     BigDecimal.valueOf(scaledToInteger).movePointLeft(scale);
 String s = fmt.format(bd);

would produce the correctly-formatted value, where scaledToInteger is the parameter supplied to this interface method.

If the format is not needed, the scale can be obtained in fewer steps by passing the Locale from step 3 directly to Currency.getInstance. That would be enough to build a simple reference implementation for this data type that would return a BigDecimal with its point moved left by the scale.

Now let's talk about step 2.

Java's locale support is based on BCP 47, a format for identifiers standardized by IETF to ensure that they are reliable and specific.

The string obtained from the lc_monetary setting in step 1 above is, most often, a string that makes sense to the underlying operating system's C library, using some syntax that predated BCP 47, and likely demonstrates all of the problems BCP 47 was created to overcome.

From a first glance at a few simple examples, it can appear that replacing some underscores with hyphens could turn some simple OS-library strings into BCP 47 tags, but that is far from the general case, which is full of nonobvious rules, special cases, and grandfather clauses.

A C library, liblangtag, is available to perform exactly that mapping, and weighs in at about two and a half megabytes. The library might be present on the system where PostgreSQL is running, in which case it could be used in step 2, at the cost of a native call.

If PostgreSQL was built with ICU, a native method could accomplish the same (as nearly as practical) thing by calling uloc_canonicalize followed by uloc_toLanguageTag; or, if the ICU4J Java library is available, ULocale.createCanonicalcould be used to the same effect.

It might be simplest to just use a native call to obtain the scaling and other needed details from the underlying operating system library.

Because of step 2's complexity, PL/Java does not here supply the simple reference implementation to BigDecimal proposed above.

  • Method Details

    • construct

      T construct(long scaledToInteger)
      Constructs a representation T from the components of the PostgreSQL data type.

      It might be necessary to extend this interface with extra parameters (or to use the Modifier mechanism) to receive the needed scaling and currency details, and require the corresponding Adapter (which could no longer be pure Java) to make the needed native calls to obtain those.

      Parameters:
      scaledToInteger - integer value that must be scaled according to the setting of the lc_monetary configuration setting, and represents a value in the currency also determined by that setting.