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 1ntoww-00013Z-Oz for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2022 11:13:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ntowv-0000rb-Ix for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2022 11:13:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntowv-0000pC-8D for pgsql-sql@lists.postgresql.org; Wed, 25 May 2022 11:13:09 +0000 Received: from mail-lj1-x22e.google.com ([2a00:1450:4864:20::22e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ntowo-0007En-MM for pgsql-sql@lists.postgresql.org; Wed, 25 May 2022 11:13:08 +0000 Received: by mail-lj1-x22e.google.com with SMTP id 1so10743487ljh.8 for ; Wed, 25 May 2022 04:13:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=mime-version:from:date:message-id:subject:to; bh=S3WSuOAot91OxnBOhdFnw2NYrIFnrd8LK1nBVrQLi1g=; b=Lv8oEg94U/cYCSpq6Q6A9/rLgzS/NpnOZO8Qyzv0/MIpNobC3kweC8eLLeYphMKXsE TnkH7LOQBQ/WSWKbTQKr27qE2EsNbn6AYtp4cbXkGTZUmAkWBX0lCZwX+xcki0cdMJF/ dFVmCXmnQUwsxe0Vkbug1s5N7d6BLw4HjA4MC6ELxgR0WXAaG4+fJDfUfBQc4I96rdop X03qQbTP/1ffQkDSC24cwy71DBLlDfqu2t1thUWJaGPSBiKa2J81NQ7aPLtSl21V7YEk +AX944yea8KmkpZPmbT45kZ4SERDiYzsopLDGzdBp05F3OEYAMuEreSnmlSCbUrmYTdk DkWw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=S3WSuOAot91OxnBOhdFnw2NYrIFnrd8LK1nBVrQLi1g=; b=em2GHoCWu8qH70JgDj54ylP4Qfjvthlyhn1FuqIB5thoKKXDFJ1zsHvD+PILrfj2aT 9hYQIO5CwLJgxK29nyH4+xizBkuQU9hG4EXRjQBNRfZ4n5VYLzYHlP5I8Zv5ZH4Wb0P8 UqQ1ERVnqDotBoSzbernJYVsn2L8ckEGrwJmRLzPt9LXPk5tJ9gH7yXiAn4gD8oo6bw+ iXNOiG2V3BVcMNVBLqVT+Wg2BZ7gz3D2QOjwwW8qxqJmUvzwl7DZa6Gdx1Y82T+VYp0w MjSAlJ1o9fcWHrmANFzBb13StaYdA5N4KpsZ7G0U3n0VXBwDtXtxaN1/vWNYisuuHtYK AezQ== X-Gm-Message-State: AOAM533eqUCSGrFTebxnTFw0ZMJ/89wTTLD58dFJsFEQW67Sq0h/GLOR iRclPv+Cz4nRwjJrnsxx9RJ/8KHwUTkgoZR1QPg99aASExUOaQ== X-Google-Smtp-Source: ABdhPJxO4qNpjzX/kfbnvMtri1QH7cdDlBNKW8kM3SdJb0FF/eneWgugzJFWAhkOsuwdlkIuQhkVl1fNrfUebvgRCCE= X-Received: by 2002:a05:651c:230c:b0:250:6459:d6d4 with SMTP id bi12-20020a05651c230c00b002506459d6d4mr11608009ljb.271.1653477180853; Wed, 25 May 2022 04:13:00 -0700 (PDT) MIME-Version: 1.0 From: Hui Jackson Date: Wed, 25 May 2022 19:12:49 +0800 Message-ID: Subject: Using Transaction with if-else in prepared statement To: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000002510c05dfd42858" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000002510c05dfd42858 Content-Type: text/plain; charset="UTF-8" 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 --00000000000002510c05dfd42858 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I am trying to make transaction=C2=A0in nodejs
The log= ic will be check if sufficient coin in sender's amount, if sufficient t= hen do transaction.
I am new to postgres, not sure if this is a r= ight way to do so, if you have a better solution, please=C2=A0let me know, = thank you.

= 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
--00000000000002510c05dfd42858--