2

My code below, keeps getting the error -

bind message supplies 1 parameters, but prepared statement "" requires 0.

Similar parametrized raw SQL query work fine when it is not wrapped in an anonymous code block (https://stackoverflow.com/a/40853818/8252769, the "DO $$...END $$"). But I need it so I can execute the INSERT conditionally in one SQL statement.

import { Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import { Connection, getRepository, Repository } from "typeorm";

.........

const queryRunner = this.connection.createQueryRunner();

    await queryRunner.connect();

    try {
        await queryRunner.query(
          `DO $$
          BEGIN
            IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = $1))
            THEN
              INSERT INTO .............
            ELSE
              RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
            END IF;
          END $$;`,
          [
            documentId,
          ],
        ),
      );
    } catch (ex) {
      throw ex;
    } finally {
      await queryRunner.release();
    }
Ken White
  • 123,280
  • 14
  • 225
  • 444
Peter_101
  • 303
  • 4
  • 13
  • 1
    A [DO](https://www.postgresql.org/docs/14/sql-do.html) block **cannot** receive parameters. Thus the reference `$1` is invalid. It may be best to write an actual stored procedure. – Belayer Apr 28 '22 at 23:45

1 Answers1

0

I just tested passing the value to the string parameter rather than as a parameters array to the query function and it seems to work:

try {
    await queryRunner.query(
        `DO $$
        BEGIN
        IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = '${documentId}'))
        THEN
            INSERT INTO .............
        ELSE
            RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
        END IF;
        END $$;`,
    ),
    );
} catch (ex) {}

Don't forget the '' around the value when you pass it as it has to be an actual string for the database.

tevvek
  • 516
  • 5
  • 13