Re: Using Transaction with if-else in prepared statement
| От | Steve Midgley |
|---|---|
| Тема | Re: Using Transaction with if-else in prepared statement |
| Дата | |
| Msg-id | CAJexoS+Xa2A1QN2BSmBKSqbKC5Yhs=K7RHqsniKonQMuFt14LA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Using Transaction with if-else in prepared statement (Hui Jackson <jackhts4@gmail.com>) |
| Список | pgsql-sql |
On Wed, May 25, 2022 at 4:13 AM Hui Jackson <jackhts4@gmail.com> wrote:
I am trying to make transaction in nodejsThe 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 = 10const senderId = 1const receiverId = 2await pgPool.query(`DO $$BEGINif (SELECT coin from app_user where id = $1) >= $3 THENUPDATE 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
For debugging, you might try to sending each select, update, and insert in separate NodeJS query statements. You can do the IF work in NodeJS via a select for data from Pg.
But at the core of your problem I believe is that you are not configuring the postgres statement to receive parameters, but your injecting parameters from Node. From the Pg docs on prepared statements: https://www.postgresql.org/docs/current/sql-prepare.html
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
В списке pgsql-sql по дате отправления: