Financial Functions

Money amount and currency functions for financial applications.

Money values are exact structs — STRUCT(amount DECIMAL(18,3), currency VARCHAR) — so arithmetic never drifts and mixed-currency operations fail loudly instead of silently producing garbage. Every function is also available under a short alias without the anofox_tab_ prefix (e.g. money, money_add, is_valid_currency).

Quick Reference

Function	Description	SQL Signature
anofox_tab_money	Create money value	(amount, currency) -> MONEY
anofox_tab_money_from_cents	Create from subunits	(cents, currency) -> MONEY
anofox_tab_money_is_positive	Check positive	(money) -> BOOLEAN
anofox_tab_money_in_range	Check range	(money, min, max) -> BOOLEAN
anofox_tab_money_format	Format for display	(money, style) -> VARCHAR
anofox_tab_is_valid_currency	Validate ISO code	(code) -> BOOLEAN

MONEY above is shorthand for STRUCT(amount DECIMAL(18,3), currency VARCHAR).

---

Constructors & Accessors (4)

anofox_tab_money

Create a money value from a decimal amount and ISO 4217 currency code.

Parameters

Parameter	Type	Required	Default	Description
amount	DOUBLE	Yes	-	Decimal amount
currency_code	VARCHAR	Yes	-	ISO 4217 code

Example

SELECT anofox_tab_money(100.50, 'USD');
-- Output: {amount: 100.500, currency: 'USD'}

anofox_tab_money_from_cents

Create a money value from an integer amount in the currency's smallest unit. The amount is divided by the currency's subunit factor (100 for USD/EUR, 1 for JPY).

Parameters

Parameter	Type	Required	Default	Description
cents	BIGINT	Yes	-	Amount in smallest currency unit
currency_code	VARCHAR	Yes	-	ISO 4217 code

Example

SELECT
    anofox_tab_money_from_cents(10050, 'USD'),  -- {amount: 100.500, currency: 'USD'}
    anofox_tab_money_from_cents(1000, 'JPY');   -- {amount: 1000.000, currency: 'JPY'} (no subunits)

anofox_tab_money_amount

Extract the exact numeric amount (DECIMAL(18,3)) from a money value.

SELECT anofox_tab_money_amount(anofox_tab_money(100.50, 'USD'));  -- 100.500

anofox_tab_money_currency

Extract the ISO 4217 currency code from a money value.

SELECT anofox_tab_money_currency(anofox_tab_money(100.50, 'USD'));  -- 'USD'

---

Validation & Properties (5)

anofox_tab_money_is_positive

Check if the amount is positive (> 0).

SELECT
    amount,
    anofox_tab_money_is_positive(anofox_tab_money(amount, currency)) as is_positive
FROM transactions;

-- 100.50  | true
-- 0.00    | false
-- -50.00  | false

anofox_tab_money_is_negative

Check if the amount is negative (< 0).

SELECT anofox_tab_money_is_negative(anofox_tab_money(-50.00, 'USD'));  -- true

anofox_tab_money_is_zero

Check if the amount is exactly zero.

SELECT anofox_tab_money_is_zero(anofox_tab_money(0.00, 'USD'));  -- true

anofox_tab_money_in_range

Check if the amount is within the inclusive range [min, max].

Parameters

Parameter	Type	Required	Default	Description
money	MONEY	Yes	-	Money value to validate
min	DOUBLE	Yes	-	Minimum valid amount
max	DOUBLE	Yes	-	Maximum valid amount

Example

SELECT
    anofox_tab_money_in_range(anofox_tab_money(100.50, 'USD'), 0.01, 999999.99),  -- true
    anofox_tab_money_in_range(anofox_tab_money(0.00, 'USD'), 0.01, 999999.99);    -- false

anofox_tab_money_same_currency

Check if two money values have the same currency code.

SELECT anofox_tab_money_same_currency(
    anofox_tab_money(100.00, 'USD'),
    anofox_tab_money(50.00, 'EUR')
);  -- false

---

Formatting (1)

anofox_tab_money_format

Format a money value as a string. Decimal places follow the currency's subunit factor (two for USD/EUR, none for JPY).

Parameters

Parameter	Type	Required	Default	Description
money	MONEY	Yes	-	Money value to format
format_style	VARCHAR	Yes	-	'symbol', 'code', or 'plain'

Example

SELECT
    anofox_tab_money_format(anofox_tab_money(1234.50, 'USD'), 'symbol'),  -- '$1,234.50'
    anofox_tab_money_format(anofox_tab_money(1234.50, 'USD'), 'code');    -- '1234.50 USD'

---

Arithmetic (4)

All arithmetic is exact DECIMAL(18,3); adding or subtracting values of different currencies raises an error.

anofox_tab_money_add

SELECT anofox_tab_money_add(
    anofox_tab_money(100.50, 'USD'),
    anofox_tab_money(50.25, 'USD')
);  -- {amount: 150.750, currency: 'USD'}

anofox_tab_money_subtract

SELECT anofox_tab_money_subtract(
    anofox_tab_money(100.50, 'USD'),
    anofox_tab_money(50.25, 'USD')
);  -- {amount: 50.250, currency: 'USD'}

anofox_tab_money_multiply

Multiply a money amount by a scalar factor.

SELECT anofox_tab_money_multiply(anofox_tab_money(100.00, 'USD'), 1.1);
-- {amount: 110.000, currency: 'USD'}

anofox_tab_money_abs

SELECT anofox_tab_money_abs(anofox_tab_money(-100.50, 'USD'));
-- {amount: 100.500, currency: 'USD'}

---

Currency Functions (3)

anofox_tab_is_valid_currency

Check if a string is a valid ISO 4217 currency code.

SELECT
    anofox_tab_is_valid_currency('USD'),  -- true
    anofox_tab_is_valid_currency('ZZZ');  -- false

anofox_tab_currency_name

Get the full English name of a currency.

SELECT anofox_tab_currency_name('USD');  -- 'US Dollar'

anofox_tab_currency_symbol

Get the currency symbol.

SELECT anofox_tab_currency_symbol('EUR');  -- '€'

Note: there is no currency conversion function — the extension deliberately works with exact single-currency values and leaves exchange rates to your own data.

---

Practical Patterns

Amount Validation Pipeline

SELECT
    invoice_id,
    amount,
    currency,
    CASE
        WHEN NOT anofox_tab_is_valid_currency(currency) THEN 'INVALID_CURRENCY'
        WHEN NOT anofox_tab_money_is_positive(anofox_tab_money(amount, currency)) THEN 'NEGATIVE_AMOUNT'
        WHEN NOT anofox_tab_money_in_range(anofox_tab_money(amount, currency), 0.01, 999999.99) THEN 'OUT_OF_RANGE'
        ELSE 'VALID'
    END as validation_status,
    anofox_tab_money_format(anofox_tab_money(amount, currency), 'symbol') as formatted_amount
FROM invoices
WHERE anofox_tab_is_valid_currency(currency);

Exact Ledger Amounts from Integer Cents

SELECT
    transaction_id,
    anofox_tab_money_from_cents(amount_cents, currency) as amount,
    anofox_tab_money_format(anofox_tab_money_from_cents(amount_cents, currency), 'code') as display
FROM ledger;

Tax Calculation

SELECT
    order_id,
    subtotal,
    tax_rate,
    anofox_tab_money_multiply(anofox_tab_money(subtotal, 'EUR'), tax_rate) as tax_amount,
    anofox_tab_money_add(
        anofox_tab_money(subtotal, 'EUR'),
        anofox_tab_money_multiply(anofox_tab_money(subtotal, 'EUR'), tax_rate)
    ) as total
FROM orders;

---