In a previous article, we look at creating a simple distributed user-defined function (UDF) in CockroachDB. In this article, we'll revisit UDFs in the form of user-defined composite types, introduced in CockroachDB v23.1.

Introduction

A composite type is simply a type composed of other types. In the following example, we are creating a composite money type. The money type is the combination of an amount, currency code and monetary type:

• The amount is a decimal with fractions matching the currency.

• The currency is a 3-letter ISO 4217 code.

• The monetary type is an arbitrary tag for denoting the type of money. For example:

  • RM for real money

  • FM for funny money

On top of the type, we'll also add a few UDFs for money arithmetics. Ideally, these functions should only be allowed when operands use the same currency and monetary type. For example, you want to prevent adding 10 USD with 15 SEK or real money with funny money. There's however no way to enforce these rules in the DB itself.

Let's begin with the money type:

CREATE TYPE money_type AS (amount decimal, currency_code char (3), monetary_type char (2));

Next, create a few UDFs for money operations:

CREATE FUNCTION money_amount(x money_type) RETURNS decimal IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
   select ((x).amount)::decimal
$$;

CREATE FUNCTION money_currency(x money_type) RETURNS char(3) IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
   select ((x).currency_code)::char(3)
$$;

CREATE FUNCTION money_monetary_type(x money_type) RETURNS char(3) IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
   select ((x).monetary_type)::char(3)
$$;

CREATE FUNCTION to_money(x string) RETURNS money_type IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
   select (
    split_part($1,' ',1)::decimal,
    split_part($1,' ',2),
    split_part($1,' ',3)
    )::money_type
$$;

Next, let's add a few money arithmetics UDFs:

CREATE FUNCTION money_add(IN m money_type, IN addend decimal) RETURNS money_type IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
select (
    (m).amount + addend,
    (m).currency_code,
    (m).monetary_type
    )
$$;

CREATE FUNCTION money_mult(IN m money_type, IN multiplier decimal) RETURNS money_type IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
select (
    (m).amount * multiplier,
    (m).currency_code,
    (m).monetary_type
    )
$$;

CREATE FUNCTION money_div(IN m money_type, IN dividend decimal) RETURNS money_type IMMUTABLE LEAKPROOF LANGUAGE SQL AS $$
select (
    (m).amount / dividend,
    (m).currency_code,
    (m).monetary_type
    )
$$;

That's about it. Now let's put the money type to use and see how things work. Create an account table holding a cached balance using the money type:

create table account
(
    id             uuid        not null default gen_random_uuid(),
    city           string      not null,
    balance        money_type  not null,
    name           string(128) not null,
    description    string(256) null,
    closed         boolean     not null default false,
    allow_negative integer     not null default 0,
    updated_at     timestamptz not null default clock_timestamp(),

    primary key (id)
);

Let's strengthen the integrity a bit with these CHECK constraints (totally optional):

alter table account
    add constraint check_account_allow_negative check (allow_negative between 0 and 1);
alter table account
    add constraint check_account_positive_balance check ((balance).amount * abs(allow_negative - 1) >= 0);
alter table account
    add constraint check_account_currency check ((balance).currency_code in ('SEK', 'USD', 'GBP', 'EUR'));

As you can see, different accounts may or may not accept a negative balance depending on the current flag. We could also have used an enum type for the currency.

Add some data:

INSERT INTO account (id, city, balance, name, allow_negative)
VALUES ('10000000-0000-0000-0000-000000000000', 'stockholm', to_money('100.00 SEK RM'), 'test:1', 0),
       ('20000000-0000-0000-0000-000000000000', 'stockholm', to_money('200.00 SEK RM'), 'test:2', 1),
       ('30000000-0000-0000-0000-000000000000', 'new york', to_money('300.00 USD PM'), 'test:3', 0),
       ('40000000-0000-0000-0000-000000000000', 'new york', to_money('400.00 USD PM'), 'test:4', 1);

Let's see how this looks:

select id,city,balance,
       money_amount(balance),
       money_currency(balance),
       money_monetary_type(balance)
from account;
                   id                  |   city    |     balance     | money_amount | money_currency | money_monetary_type
---------------------------------------+-----------+-----------------+--------------+----------------+----------------------
  10000000-0000-0000-0000-000000000000 | stockholm | (100.00,SEK,RM) |       100.00 | SEK            | RM
  20000000-0000-0000-0000-000000000000 | stockholm | (200.00,SEK,RM) |       200.00 | SEK            | RM
  30000000-0000-0000-0000-000000000000 | new york  | (300.00,USD,PM) |       300.00 | USD            | PM
  40000000-0000-0000-0000-000000000000 | new york  | (400.00,USD,PM) |       400.00 | USD            | PM
(4 rows)

Time: 14ms total (execution 14ms / network 0ms)

Let's execute an aggregation query:

select sum(money_amount(balance)) balance, 
       money_currency(balance) currency 
from account group by balance,currency;

  balance | currency
----------+-----------
   100.00 | SEK
   200.00 | SEK
   300.00 | USD
   400.00 | USD
(4 rows)

Time: 2ms total (execution 2ms / network 0ms)

Updating the money type can be done using one of the arithmetic functions:

UPDATE account set balance=money_add(balance,-90.00) where id='10000000-0000-0000-0000-000000000000';
UPDATE account set balance=money_add(balance,-100.00) where id='20000000-0000-0000-0000-000000000000';

Conclusion

This article provides an example of how to create a user-defined composite type in CockroachDB v23.1, specifically a money type composed of an amount, currency code, and monetary type. It also explains how to use UDFs for money operations, create a table to hold a cached balance, and add CHECK constraints to strengthen the integrity.