Using Transaction with if-else in prepared statement

From: Hui Jackson <jackhts4(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Using Transaction with if-else in prepared statement
Date: 2022-05-25 11:12:49
Message-ID: CAHXAyjx-2MhO0zoCt3Vr_2AW6sFVjN0tgDo_GPVg=DVCzQDQMQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I am trying to make transaction in nodejs
The logic will be check if sufficient coin in sender's amount, if
sufficient then do transaction.
I am new to postgres, not sure if this is a right way to do so, if you have
a better solution, please let me know, thank you.

const coin = 10
const senderId = 1
const receiverId = 2
await pgPool.query(`
DO $$
BEGIN
if (SELECT coin from app_user where id = $1) >= $3 THEN
UPDATE app_user set coin = coin - $3 where id = $1;
UPDATE app_user set coin = coin + $3 where id = $2;
INSERT INTO coin_history(sender_id, receiver_id, amount) VALUES
($1, $2, $3)

END IF;
END;
$$`,[senderId, receiverId, coin]);

Error (node:10044) UnhandledPromiseRejectionWarning: error: bind message
supplies 3 parameters, but prepared statement "" requires 0

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Steve Midgley 2022-05-25 18:03:34 Re: Using Transaction with if-else in prepared statement
Previous Message Frank Streitzig 2022-05-23 20:39:19 Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?