Scalar Functions

Accessor Functions

monetary_amount

Returns the raw integer amount (in minor units):

monetary_amount(value) → BIGINT

SELECT monetary_amount('123.45 EUR'::monetary) AS cents;

Run Reset

monetary_currency

Returns the currency code:

monetary_currency(value) → VARCHAR

SELECT monetary_currency('100 USD'::monetary) AS currency;

Run Reset

monetary_decimal

Returns the amount as a decimal (with proper precision):

monetary_decimal(value) → DOUBLE

SELECT monetary_decimal('99.99 EUR'::monetary) AS amount;

Run Reset

Constructor Functions

make_monetary

Creates a monetary value from components:

make_monetary(amount BIGINT, currency VARCHAR) → monetary
make_monetary(amount DOUBLE, currency VARCHAR) → monetary

-- From cents (integer) SELECT make_monetary(12345, 'EUR') AS from_cents;

Run Reset

-- From decimal amount SELECT make_monetary(123.45, 'USD') AS from_decimal;

Run Reset

parse_monetary

Parses a string into a monetary value:

parse_monetary(text VARCHAR) → monetary

SELECT parse_monetary('€99.99') AS parsed, parse_monetary('USD 50') AS prefix, parse_monetary('100 GBP') AS suffix;

Run Reset

Country/Currency Functions

currency_for_country

Returns the primary currency for a country code:

currency_for_country(country_code VARCHAR) → VARCHAR

SELECT currency_for_country('DE') AS germany, currency_for_country('JP') AS japan, currency_for_country('US') AS usa;

Run Reset

countries_for_currency

Returns all countries using a currency:

countries_for_currency(currency_code VARCHAR) → VARCHAR[]

SELECT countries_for_currency('EUR') AS eurozone;

Run Reset

Summary

Function	Input	Output	Description
monetary_amount(m)	monetary	BIGINT	Raw amount in minor units
monetary_currency(m)	monetary	VARCHAR	Currency code
monetary_decimal(m)	monetary	DOUBLE	Decimal amount
make_monetary(amt, cur)	BIGINT/DOUBLE, VARCHAR	monetary	Create from parts
parse_monetary(text)	VARCHAR	monetary	Parse string
currency_for_country(cc)	VARCHAR	VARCHAR	Country → currency
countries_for_currency(cc)	VARCHAR	VARCHAR[]	Currency → countries