Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntvMV-0006l7-Vs for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2022 18:04:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ntvMU-0001UJ-J6 for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2022 18:03:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntvMU-0001U4-6P for pgsql-sql@lists.postgresql.org; Wed, 25 May 2022 18:03:58 +0000 Received: from premium22-1.web-hosting.com ([68.65.122.103]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntvMQ-0006Pd-VR for pgsql-sql@lists.postgresql.org; Wed, 25 May 2022 18:03:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=c4iay49VN21lXdhJCqLgBQ6Sme8pfP90H/UuasL6C3I=; b=wCnbm9iuyZ84w0oLwfSFcQGUxp ihfGxpBxp8EYP9OhdGNYChWuhxJ/Hz18Q21DRnz7qYRb4JwQ7/QxrVR19bLiIqT0kcIp5lyuF+Rdf LEEMl/Vi8SMevTNEu8CDHOcuGxqkvuuA6QWUWpeHnybWxIhOd4j7CDE63ETTJW3M8nnN4j/DSZXJU EQk+dJR0lvl5czjuiRtz+YzApiHriGo2C2kgsw7BSD+liMDUKTKE4ExM/F6+08JZLik2jp2bO/w/X LeTfdfCxiglx35ZBmme/pjvT5J9IyNA/6PVLPFbbGarJb4u2f9AglDtjfmmLlk2hAjVBqAhcV2ozR Ocka+PxQ==; Received: from mail-pj1-f45.google.com ([209.85.216.45]:42758) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.95) (envelope-from ) id 1ntvMI-00BtDg-1Y for pgsql-sql@lists.postgresql.org; Wed, 25 May 2022 14:03:51 -0400 Received: by mail-pj1-f45.google.com with SMTP id cs3-20020a17090af50300b001e0808b5838so4863170pjb.1 for ; Wed, 25 May 2022 11:03:45 -0700 (PDT) X-Gm-Message-State: AOAM5323qI5wgu2ZhuEIRreWsb/kUUZUSdZl82aTAHrN7sVxmptd6xRh IIGA0BFDLR2thwNozjdgCBxmmMWOYxZzDwwFKIA= X-Google-Smtp-Source: ABdhPJwcJEPl8HCAIKlnOBXX7Y0UrUm+f3JSQX5H5szuKkbUfNAMhOeBo4TOyDLyGjZs3FTH3n0gwWyHChywTTy25k4= X-Received: by 2002:a17:902:da8b:b0:162:6c7:7254 with SMTP id j11-20020a170902da8b00b0016206c77254mr22285491plx.150.1653501825041; Wed, 25 May 2022 11:03:45 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Wed, 25 May 2022 11:03:34 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Using Transaction with if-else in prepared statement To: Hui Jackson Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000eaccbb05dfd9e4eb" X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eaccbb05dfd9e4eb Content-Type: text/plain; charset="UTF-8" On Wed, May 25, 2022 at 4:13 AM Hui Jackson wrote: > 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 > 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); --000000000000eaccbb05dfd9e4eb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, May 25, 2022 at 4:13 AM Hui J= ackson <jackhts4@gmail.com>= wrote:
I am trying to make transaction=C2=A0in nodejs
The logic will = be check if sufficient coin in sender's amount, if sufficient then do t= ransaction.
I am new to postgres, not sure if this is a right way= to do so, if you have a better solution, please=C2=A0let me know, thank yo= u.

= const coin =3D 10
const senderId =3D 1
const receiverId =3D 2
await pgPool.query(`
DO $$
=C2=A0 =C2=A0 BEGIN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 if (SELECT coin fr= om app_user where id =3D $1) >=3D $3 THEN
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 UPDAT= E app_user set coin =3D coin - $3 where id =3D $1;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 U= PDATE app_user set coin =3D coin + $3 where id =3D $2;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 INSERT INTO coin_history(sender_id, receiver_id, amount) VALUES ($1, $2= , $3)

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 END IF;
=C2=A0 =C2=A0 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 st= atements. 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 y= ou are not configuring the postgres statement to receive parameters, but yo= ur injecting parameters from Node. From the Pg docs on prepared statements:= =C2=A0= https://www.postgresql.org/docs/current/sql-prepare.html

=
PREPARE fooplan (int, text, bool, numeric) AS
=C2=A0 =C2=A0 I= NSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Va= lley', 't', 200.00);
=C2=A0
--000000000000eaccbb05dfd9e4eb--