0

I use Javascript and Postgresql. I want do transaction:


const user = await getCurrentUser()

await db.query(sql`
    BEGIN;
    DO
    $$
      DECLARE
        round int;
      BEGIN

        SELECT id
        INTO round
        FROM "game_round"
        WHERE status = 'pending';

        IF NOT FOUND
        THEN
          RAISE EXCEPTION 'No pending round found';
        ELSE

          INSERT INTO "game_round_bet"
            (game_round_id, user_id, money)
          VALUES
            (round, ${user.id}, 300);
        END IF;
      END;
    $$;
    COMMIT;
`)

But if i pass ${user.id} javascript variable to sql string, i get error:

"Cannot insert multiple commands into a prepared statement"

How to pass variable from Javascript world to SQL world?

Алексей Соснин
  • 1,513
  • 7
  • 12
  • Put the code into a function, then call the function. But you can also do this without PL/pgSQL at all, see e.g. [here](https://stackoverflow.com/questions/6722344) –  Jun 25 '20 at 17:08
  • @a_horse_with_no_name i love see SQL code and write it in javascript files, i not love create sql functions, cuz i not see its code, can i do this without sql functions? just by DO operator? – Алексей Соснин Jun 25 '20 at 17:11
  • As I said: you don't need PL/pgSQL or a DO block. This can be done with a single statement. See the link in my first comment –  Jun 25 '20 at 17:38
  • @a_horse_with_no_name how i can throw exception with custom text if first select return 0 rows without PL/pgSQL? Literraly question. Can i do this without PL/pgSQL? No. therefore, it makes no sense to me to read your link a second time. =)) but thank you so much for trying to help – Алексей Соснин Jun 25 '20 at 17:42
  • @a_horse_with_no_name I understand that my javascript sql string tag modifies the sql code, and adds variable substitution there. But he does not expect that I will SUCH sql code to execute, therefore, incorrect SQL generates. – Алексей Соснин Jun 25 '20 at 17:46

0 Answers0