PostgreSQL Scripting Tips

I have been working on a double-entry ledger implementation in PostgreSQL called pgledger, and I wanted to write some example scripts. pgledger is written in SQL and meant to be used from any language or platform where you can call SQL functions, so I wanted the examples to be pure SQL.

Here are a few tips I discovered along the way. The sections are relatively independent:

• Storing Response Variables with \gset
• Transposing Data with \crosstabview
• Showing SQL Statements and Output in the Same File

Storing Response Variables with \gset

At first, I found it difficult to write SQL scripts given the random nature of pgledger function responses. Each function call would generate new random identifiers, so I couldn’t just write a static set of SQL statements. (See here for more about how I generate random IDs for pgleger).

This is when I discovered the psql command \gset. \gset executes a SQL statement and stores the result in a local variable. For example:

pgledger=# SELECT id FROM pgledger_create_account('user1.external', 'USD') \gset

And then id can be used in followup SQL statements as :'id':

pgledger=# select :'id';
            ?column?
---------------------------------
 pgla_01KE4VY0XGE7FTJY9FVYV2689N
(1 row)

Variables can be named inline or by setting a prefix as an argument to \gset:

pgledger=# SELECT id AS user1_external_id FROM pgledger_create_account('user1.external', 'USD') \gset

pgledger=# SELECT id FROM pgledger_create_account('user1.receivables', 'USD') \gset user1_receivables_

pgledger=# select :'user1_external_id', :'user1_receivables_id';
            ?column?             |            ?column?
---------------------------------+---------------------------------
 pgla_01KE4W21EXFWHASSZKMSGH6RT2 | pgla_01KE4W25Y5FVF9W256JE7RYJZP
(1 row)

For more information, check out the PostgreSQL docs at \gset or see how I use it in the pgledger examples.

Transposing Data with \crosstabview

Once data is recorded in a ledger, one of the ways to visualize it is by tracing one flow across many accounts. For example, a single payment might first be recorded as a receivable (expecting money), then as available funds, and finally as partially or fully refunded. If the payment ID is recorded in metadata, a simple query in pgledger might look like this:

SELECT
    e.transfer_id,
    a.name,
    e.amount
FROM pgledger_entries_view e
INNER JOIN pgledger_accounts_view a ON e.account_id = a.id
WHERE e.metadata ->> 'payment_id' = 'p_123'
ORDER BY e.transfer_id;

           transfer_id           |          name          | amount
---------------------------------+------------------------+--------
 pglt_01KE4XK326F8PB40DKGMW4V7J3 | user1.external         | -50.00
 pglt_01KE4XK326F8PB40DKGMW4V7J3 | user1.receivables      |  50.00
 pglt_01KE4XK328F5EBTFS44PMV4NNF | user1.receivables      | -49.50
 pglt_01KE4XK328F5EBTFS44PMV4NNF | user1.available        |  49.50
 pglt_01KE4XK329ENCRYKCTYWSW7JMG | user1.available        | -20.00
 pglt_01KE4XK329ENCRYKCTYWSW7JMG | user1.pending_outbound |  20.00
 pglt_01KE4XK329FGK92HRWTEARYBVQ | user1.pending_outbound | -20.00
 pglt_01KE4XK329FGK92HRWTEARYBVQ | user1.external         |  20.00
(8 rows)

This shows all of the account movements, but it can be hard to visualize in this form as rows. Since each transfer in the ledger is made up of multiple entries (e.g. from and to), the query results show multiple rows for each transfer (as you can see by the duplicate transfer_id values).

With data like this, I find that transposing (or rotating) the output is often a better way to visualize it. Each account becomes a column, and the logical movements each become a single row affecting multiple columns.

In psql, this can be done with the \crosstabview command. This is as simple as adding \crosstabview to the end of the SQL statement:

SELECT
    e.transfer_id,
    a.name,
    e.amount
FROM pgledger_entries_view e
INNER JOIN pgledger_accounts_view a ON e.account_id = a.id
WHERE e.metadata ->> 'payment_id' = 'p_123'
ORDER BY e.transfer_id \crosstabview

           transfer_id           | user1.external | user1.receivables | user1.available | user1.pending_outbound
---------------------------------+----------------+-------------------+-----------------+------------------------
 pglt_01KE4XK326F8PB40DKGMW4V7J3 |         -50.00 |             50.00 |                 |
 pglt_01KE4XK328F5EBTFS44PMV4NNF |                |            -49.50 |           49.50 |
 pglt_01KE4XK329ENCRYKCTYWSW7JMG |                |                   |          -20.00 |                  20.00
 pglt_01KE4XK329FGK92HRWTEARYBVQ |          20.00 |                   |                 |                 -20.00
(4 rows)

Now, each transfer is a single row, and each account is a column. Reading down, you can easily see which accounts were affected for each transfer.

For more information, check out the PostgreSQL docs at \crosstabview or see how I use it in the pgledger reconciliation examples.

Showing SQL Statements and Output in the Same File

The last trick I discovered was showing both SQL input and output in the same file. I didn’t want folks to read the examples without understanding what each SQL query returned.

I did this by writing each example in one file (e.g. basic-example.sql) and then running that file through psql with the --echo-all flag. This echoed both the input SQL and the output results, which I wrote to a new file (e.g. basic-example.sql.out). This produces a single file for review, which even includes the comments from the original file.

Check out the resulting .out files in the pgledger examples, or the scripting behind them in the justfile.