multi-currency.sql.out

-- This file contains the sql queries plus their output, but we set the filetype to sql for better syntax highlighting
-- vim: set filetype=sql:

-- This is a fully working example script which demonstrates how to separate
-- accounts by currency and transfer between them.
--
-- Note that it uses `\gset` to store sql responses as variables. For example,
-- `\gset foo_` creates variables for each column in the response like
-- `foo_col1`, `foo_col2`, etc. These variables can then be used like
-- `:'foo1_col`.
-- The entire script can be passed to psql. If you are running postgres via the
-- pgledger docker compose, you can run this script with:
--
--   cat multi-currency.sql | \
--     docker compose exec --no-TTY postgres psql -U pgledger --echo-all --no-psqlrc
--
-- We're going to simulate a user holding balances in multiple currencies.
-- First, we create an account per currency for the user, since each account is
-- tied to a single currency. One strategy is to use hierarchical account
-- naming to make it clear these accounts are related:
SELECT id FROM pgledger_create_account('user2.usd', 'USD') \gset user2_usd_
SELECT id FROM pgledger_create_account('user2.eur', 'EUR') \gset user2_eur_
-- This style of naming makes it easy to see related accounts:
SELECT * FROM pgledger_accounts_view
WHERE name LIKE 'user2.%';
               id                |   name    | currency | balance | version | allow_negative_balance | allow_positive_balance | metadata |          created_at           |          updated_at           
---------------------------------+-----------+----------+---------+---------+------------------------+------------------------+----------+-------------------------------+-------------------------------
 pgla_01KEA9YZF8FT3A8K6JJX625GYX | user2.usd | USD      |       0 |       0 | t                      | t                      |          | 2026-01-06 18:43:59.080008+00 | 2026-01-06 18:43:59.080008+00
 pgla_01KEA9YZF9F9T8QQFA7QYVDP42 | user2.eur | EUR      |       0 |       0 | t                      | t                      |          | 2026-01-06 18:43:59.081588+00 | 2026-01-06 18:43:59.081588+00
(2 rows)

-- And you can even use PostgreSQL's ltree functionality for querying
--   https://www.postgresql.org/docs/current/ltree.html
CREATE EXTENSION ltree;
CREATE EXTENSION
SELECT * FROM pgledger_accounts_view
WHERE name::LTREE <@ 'user2';
               id                |   name    | currency | balance | version | allow_negative_balance | allow_positive_balance | metadata |          created_at           |          updated_at           
---------------------------------+-----------+----------+---------+---------+------------------------+------------------------+----------+-------------------------------+-------------------------------
 pgla_01KEA9YZF8FT3A8K6JJX625GYX | user2.usd | USD      |       0 |       0 | t                      | t                      |          | 2026-01-06 18:43:59.080008+00 | 2026-01-06 18:43:59.080008+00
 pgla_01KEA9YZF9F9T8QQFA7QYVDP42 | user2.eur | EUR      |       0 |       0 | t                      | t                      |          | 2026-01-06 18:43:59.081588+00 | 2026-01-06 18:43:59.081588+00
(2 rows)

-- Now, we can see that pgledger prevents transfers between accounts of different currencies:
SELECT * FROM pgledger_create_transfer(:'user2_usd_id',:'user2_eur_id', 10.00);
ERROR:  Cannot transfer between different currencies (USD and EUR)
CONTEXT:  PL/pgSQL function pgledger_create_transfers(transfer_request[],timestamp with time zone,jsonb) line 64 at RAISE
SQL statement "SELECT * FROM pgledger_create_transfers(
        transfer_requests => array[(from_account_id, to_account_id, amount)::TRANSFER_REQUEST],
        event_at => event_at,
        metadata => metadata
    )"
PL/pgSQL function pgledger_create_transfer(text,text,numeric,timestamp with time zone,jsonb) line 4 at RETURN QUERY
-- Instead, we need to create liquidity accounts per currency and use those for the transfers:
SELECT id FROM pgledger_create_account('liquidity.usd', 'USD') \gset liquidity_usd_
SELECT id FROM pgledger_create_account('liquidity.eur', 'EUR') \gset liquidity_eur_
-- Now, a currency conversion consist of 2 transfers using the 4 accounts. The
-- difference between these two different amounts (10.00 vs 9.26) is the
-- exchange rate.
SELECT * FROM pgledger_create_transfers(
    (:'user2_usd_id',:'liquidity_usd_id', '10.00'),
    (:'liquidity_eur_id',:'user2_eur_id', '9.26')
);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |           event_at            | metadata 
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+-------------------------------+----------
 pglt_01KEA9YZFMEKVTDAQ802VC8FYK | pgla_01KEA9YZF8FT3A8K6JJX625GYX | pgla_01KEA9YZFJFJJTT03G5J5CF5KV |  10.00 | 2026-01-06 18:43:59.092073+00 | 2026-01-06 18:43:59.092073+00 | 
 pglt_01KEA9YZFMFKYAYY846EPK85RT | pgla_01KEA9YZFKF58SJ90EF492618M | pgla_01KEA9YZF9F9T8QQFA7QYVDP42 |   9.26 | 2026-01-06 18:43:59.092073+00 | 2026-01-06 18:43:59.092073+00 | 
(2 rows)

-- Note that this used the plural `pgledger_create_transfers` instead of the
-- singular `pgledger_create_transfer` function. It is also possible to call
-- `pgledger_create_transfer` twice in a database transaction, but that is more
-- likely to result in deadlocks since bidrectional transfers will lock the
-- same accounts in reverse order.
-- It is also possible to specify the event_at and metadata with
-- `pgledger_create_transfers` using named arguments:
SELECT * FROM pgledger_create_transfers(
    event_at => '2025-07-21T12:45:54.123Z',
    metadata => '{"external_id": "ext_123"}',
    transfer_requests => ARRAY[
        (:'user2_usd_id',:'liquidity_usd_id', '10.00'),
        (:'liquidity_eur_id',:'user2_eur_id', '9.26')
    ]::TRANSFER_REQUEST []
);
               id                |         from_account_id         |          to_account_id          | amount |          created_at           |          event_at          |          metadata          
---------------------------------+---------------------------------+---------------------------------+--------+-------------------------------+----------------------------+----------------------------
 pglt_01KEA9YZFNEXCRC62N18GDMDA0 | pgla_01KEA9YZF8FT3A8K6JJX625GYX | pgla_01KEA9YZFJFJJTT03G5J5CF5KV |  10.00 | 2026-01-06 18:43:59.093283+00 | 2025-07-21 12:45:54.123+00 | {"external_id": "ext_123"}
 pglt_01KEA9YZFNFBX8MJXW0P88HKJ5 | pgla_01KEA9YZFKF58SJ90EF492618M | pgla_01KEA9YZF9F9T8QQFA7QYVDP42 |   9.26 | 2026-01-06 18:43:59.093283+00 | 2025-07-21 12:45:54.123+00 | {"external_id": "ext_123"}
(2 rows)

-- Here is what the transfers look like holistically:
SELECT
    t.id,
    t.created_at,
    t.event_at,
    acc_from.name AS acc_from,
    acc_to.name AS acc_to,
    t.amount
FROM pgledger_transfers_view t
LEFT JOIN pgledger_accounts_view acc_from ON t.from_account_id = acc_from.id
LEFT JOIN pgledger_accounts_view acc_to ON t.to_account_id = acc_to.id
WHERE acc_from.name LIKE 'user2.%' OR acc_from.name LIKE 'liquidity.%';
               id                |          created_at           |           event_at            |   acc_from    |    acc_to     | amount 
---------------------------------+-------------------------------+-------------------------------+---------------+---------------+--------
 pglt_01KEA9YZFMEKVTDAQ802VC8FYK | 2026-01-06 18:43:59.092073+00 | 2026-01-06 18:43:59.092073+00 | user2.usd     | liquidity.usd |  10.00
 pglt_01KEA9YZFMFKYAYY846EPK85RT | 2026-01-06 18:43:59.092073+00 | 2026-01-06 18:43:59.092073+00 | liquidity.eur | user2.eur     |   9.26
 pglt_01KEA9YZFNEXCRC62N18GDMDA0 | 2026-01-06 18:43:59.093283+00 | 2025-07-21 12:45:54.123+00    | user2.usd     | liquidity.usd |  10.00
 pglt_01KEA9YZFNFBX8MJXW0P88HKJ5 | 2026-01-06 18:43:59.093283+00 | 2025-07-21 12:45:54.123+00    | liquidity.eur | user2.eur     |   9.26
(4 rows)