I am trying to code a bank transfer service using Elixir/Erlang, Postgrex and PostgreSQL.

Erlang v 26
Elixir v 1.16.0
Postgrex v 0.17.4

The logic I am trying to build is the following, wrapped in a transaction BEGIN - COMMIT.

Party A seeks to transfer $50 from his account to Party B, her account. Think a Venmo transfer from Party A to Party B for $50.

Initialize

1. Store the transfer amount in an Elixir variable *transfer_amount
2. Store the from account UID in an Elixir variable from_account
3. Store the to account UID in an Elixir Variable to_account
4. Store the authorization token in an Elixir variable auth_token

BEGIN the transaction

1. Read the master token value from the authorization data table and if the auth_token = master_token, then continue, else quit
2. Read the balance from Party A’s account using the from_account UID
3. If from_account** balance is >= than transfer_amount, then continue, else quit
4. Update from_account balance by subtracting transfer_amount
5. Update to_account balance by adding transfer_amount to balance
   COMMIT the transaction

What I tried is wrapping the SQL statement built from the above into a query and then use Postgrex.query - see below. The code returns an error.

My question for the group is this the correct/best design pattern to try and accomplish the objective - a P2P bank transfer, to wrap the SQL logic into a SQL statement that has a series of “reads” and then a “read / write” using Postgrex.query! Does Postgres support “Transaction” syntax? Can the read, write, comparisons all be included into a single statement and then sent using Postgrex.query?

24)> Postgrex.query!(pid3, "BEGIN; SELECT token_masterid FROM token; IF token_id = master_token THEN SELECT from_account, balance_avail FROM main_account … [note not……; COMMIT", [])

** (Postgrex.Error) ERROR 42601 

A more simple version produces this error

iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement

    iex(14)> Postgrex.query!(pid3, "BEGIN transaction; SELECT * FROM account; COMMIT;", [])
** (Postgrex.Error) ERROR 42601 (syntax_error) cannot insert multiple commands into a prepared statement

Thank you in advance any thoughts and help !!