Skip to main content

Financial Functions

Money amount and currency functions for financial applications.

Amount Functions (6)​

anofox_money_is_positive​

Check if amount is positive (> 0).

Signature:

anofox_money_is_positive(amount DECIMAL) → BOOLEAN

Returns: TRUE if > 0, FALSE if ≤ 0, NULL if NULL

Example:

SELECT
    amount,
    anofox_money_is_positive(amount) as is_positive
FROM transactions;

-- Output:
-- 100.50  | true
-- 0.00    | false
-- -50.00  | false
-- NULL    | NULL

anofox_money_is_valid_amount​

Check if amount is within valid range.

Signature:

anofox_money_is_valid_amount(amount DECIMAL, min DECIMAL, max DECIMAL) → BOOLEAN

Parameters:

  • amount: Amount to validate
  • min: Minimum valid amount
  • max: Maximum valid amount

Returns: TRUE if min ≤ amount ≤ max

Example:

SELECT
    anofox_money_is_valid_amount(100.50, 0.01, 999999.99),    -- true
    anofox_money_is_valid_amount(0.00, 0.01, 999999.99),      -- false (< min)
    anofox_money_is_valid_amount(1000000.00, 0.01, 999999.99); -- false (> max)

anofox_money_normalize​

Normalize amount to specific decimal places.

Signature:

anofox_money_normalize(amount DECIMAL, decimals INTEGER) → DECIMAL

Parameters:

  • amount: Amount to normalize
  • decimals: Number of decimal places

Returns: Rounded and formatted amount

Example:

SELECT
    anofox_money_normalize(100.556, 2),   -- 100.56
    anofox_money_normalize(100.1, 2),     -- 100.10
    anofox_money_normalize(100.123456, 2); -- 100.12

anofox_money_format​

Format amount with currency symbol.

Signature:

anofox_money_format(amount DECIMAL, currency VARCHAR) → VARCHAR

Parameters:

  • amount: Amount to format
  • currency: ISO 4217 currency code

Returns: Formatted string (e.g., '$100.50')

Example:

SELECT
    anofox_money_format(100.50, 'USD'),  -- '$100.50'
    anofox_money_format(100.50, 'EUR'),  -- '€100,50' (region-specific)
    anofox_money_format(100.50, 'GBP');  -- '£100.50'

anofox_money_abs​

Get absolute value (remove negative sign).

Signature:

anofox_money_abs(amount DECIMAL) → DECIMAL

Returns: Absolute value

Example:

SELECT
    anofox_money_abs(100.50),   -- 100.50
    anofox_money_abs(-100.50),  -- 100.50
    anofox_money_abs(-0.01);    -- 0.01

anofox_money_round​

Round to nearest cent (or other decimal place).

Signature:

anofox_money_round(amount DECIMAL, decimals INTEGER) → DECIMAL

Parameters:

  • amount: Amount to round
  • decimals: Decimal places (typically 2 for currencies)

Returns: Rounded amount

Example:

SELECT
    anofox_money_round(100.556, 2),   -- 100.56
    anofox_money_round(100.544, 2),   -- 100.54
    anofox_money_round(100.5, 2);     -- 100.50

Currency Functions (5)​

anofox_currency_is_valid​

Check if currency code is valid ISO 4217.

Signature:

anofox_currency_is_valid(code VARCHAR) → BOOLEAN

Returns: TRUE if valid ISO 4217 code

Example:

SELECT
    anofox_currency_is_valid('USD'),  -- true
    anofox_currency_is_valid('EUR'),  -- true
    anofox_currency_is_valid('XXX');  -- false

anofox_currency_get_name​

Get currency name.

Signature:

anofox_currency_get_name(code VARCHAR) → VARCHAR

Returns: Full currency name

Example:

SELECT
    anofox_currency_get_name('USD'),  -- 'US Dollar'
    anofox_currency_get_name('EUR'),  -- 'Euro'
    anofox_currency_get_name('GBP');  -- 'British Pound'

anofox_currency_get_symbol​

Get currency symbol.

Signature:

anofox_currency_get_symbol(code VARCHAR) → VARCHAR

Returns: Currency symbol

Example:

SELECT
    anofox_currency_get_symbol('USD'),  -- '$'
    anofox_currency_get_symbol('EUR'),  -- '€'
    anofox_currency_get_symbol('GBP');  -- '£'

anofox_currency_get_decimals​

Get standard decimal places for currency.

Signature:

anofox_currency_get_decimals(code VARCHAR) → INTEGER

Returns: Number of decimal places (usually 2)

Example:

SELECT
    anofox_currency_get_decimals('USD'),  -- 2
    anofox_currency_get_decimals('JPY'),  -- 0 (no decimal)
    anofox_currency_get_decimals('KWD');  -- 3

anofox_currency_is_crypto​

Check if currency code is cryptocurrency.

Signature:

anofox_currency_is_crypto(code VARCHAR) → BOOLEAN

Returns: TRUE if cryptocurrency (BTC, ETH, etc.)

Example:

SELECT
    anofox_currency_is_crypto('BTC'),  -- true
    anofox_currency_is_crypto('ETH'),  -- true
    anofox_currency_is_crypto('USD');  -- false

Conversion & Arithmetic (6)​

anofox_currency_convert​

Convert amount from one currency to another.

Signature:

anofox_currency_convert(amount DECIMAL, from_code VARCHAR, to_code VARCHAR) → DECIMAL

Parameters:

  • amount: Amount to convert
  • from_code: Source currency code
  • to_code: Target currency code

Returns: Converted amount (using current exchange rates)

Speed: ~50ms (calls exchange rate API)

Example:

SELECT
    anofox_currency_convert(100, 'USD', 'EUR'),   -- ~92 (approximate)
    anofox_currency_convert(100, 'GBP', 'USD'),   -- ~127 (approximate)
    anofox_currency_convert(100, 'USD', 'USD');   -- 100 (same currency)

anofox_money_add​

Add two amounts.

Signature:

anofox_money_add(a DECIMAL, b DECIMAL) → DECIMAL

Returns: Sum of a + b

Example:

SELECT anofox_money_add(100.50, 50.25);  -- 150.75

anofox_money_subtract​

Subtract amounts.

Signature:

anofox_money_subtract(a DECIMAL, b DECIMAL) → DECIMAL

Returns: Difference a - b

Example:

SELECT anofox_money_subtract(100.50, 50.25);  -- 50.25

anofox_money_multiply​

Multiply amount by factor.

Signature:

anofox_money_multiply(amount DECIMAL, factor DECIMAL) → DECIMAL

Returns: Product amount × factor

Example:

SELECT
    anofox_money_multiply(100.00, 1.1),  -- 110.00 (10% increase)
    anofox_money_multiply(100.00, 0.9);  -- 90.00 (10% decrease)

anofox_money_divide​

Divide amount by divisor.

Signature:

anofox_money_divide(amount DECIMAL, divisor DECIMAL) → DECIMAL

Returns: Quotient amount / divisor

Example:

SELECT
    anofox_money_divide(100.00, 2),  -- 50.00
    anofox_money_divide(100.00, 3);  -- 33.33 (rounded)

anofox_money_percentage​

Calculate percentage of amount.

Signature:

anofox_money_percentage(amount DECIMAL, percent DECIMAL) → DECIMAL

Parameters:

  • amount: Base amount
  • percent: Percentage (0-100)

Returns: Percentage amount

Example:

SELECT
    anofox_money_percentage(100.00, 10),   -- 10.00 (10% of 100)
    anofox_money_percentage(100.00, 5),    -- 5.00 (5% of 100)
    anofox_money_percentage(200.00, 15);   -- 30.00 (15% of 200)

Practical Patterns​

Multi-Currency Consolidation​

-- Convert all transactions to EUR
CREATE TABLE transactions_eur AS
SELECT
    transaction_id,
    amount,
    currency,
    anofox_currency_convert(amount, currency, 'EUR') as amount_eur,
    CURRENT_DATE as conversion_date
FROM transactions
WHERE anofox_currency_is_valid(currency);

Amount Validation Pipeline​

-- Validate amounts in invoice processing
CREATE TABLE invoices_validated AS
SELECT
    invoice_id,
    amount,
    currency,
    CASE
        WHEN NOT anofox_currency_is_valid(currency) THEN 'INVALID_CURRENCY'
        WHEN NOT anofox_money_is_positive(amount) THEN 'NEGATIVE_AMOUNT'
        WHEN NOT anofox_money_is_valid_amount(amount, 0.01, 999999.99) THEN 'OUT_OF_RANGE'
        ELSE 'VALID'
    END as validation_status,
    anofox_money_format(amount, currency) as formatted_amount
FROM invoices;

Tax Calculation​

-- Calculate tax on purchase
SELECT
    order_id,
    subtotal,
    tax_rate,
    anofox_money_percentage(subtotal, tax_rate) as tax_amount,
    anofox_money_add(subtotal, anofox_money_percentage(subtotal, tax_rate)) as total_with_tax
FROM orders;

Foreign Exchange Report​

-- Consolidate revenue by currency and convert to main currency
CREATE TABLE revenue_summary AS
SELECT
    currency,
    SUM(amount) as revenue_original,
    anofox_currency_get_name(currency) as currency_name,
    anofox_currency_get_symbol(currency) as symbol,
    SUM(anofox_currency_convert(amount, currency, 'USD')) as revenue_usd
FROM transactions
WHERE anofox_currency_is_valid(currency)
GROUP BY 1, 3, 4
ORDER BY revenue_usd DESC;

Performance Tips​

  • Batch conversions: Convert multiple amounts at once
  • Cache exchange rates: Store conversion results for same rate period
  • Normalize decimals: Always use 2 decimals for standard currencies
  • Validate early: Check currency code before conversion

Next Steps​

🍪 Cookie Settings