(node:533) UnhandledPromiseRejectionWarning: RangeError: Variable $1 out of range. Parameters array length: 0

[IsaiahJTurner]

Summary

I need to run this SQL for AWS RDS to setup postgis but it fails with the following error. I've tried using scripts as well as db.query.

Example

Code demonstrating the behavior

db.query(`CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;`)

I also tried

  const { fd, path, cleanup } = await file()
  fs.writeFileSync(fd, `
    CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
  `)
  const sql = new db.pgp.QueryFile(path, { minify: true })
  await db.query(sql)
  cleanup()

Expected behavior

Creates the function

Actual behavior

(node:533) UnhandledPromiseRejectionWarning: RangeError: Variable $1 out of range. Parameters array length: 0

[vitaly-t]

Your code examples and the error do not add up. The only way such an error would pop-up, if you were to pass in [] as the formatting parameter into the query method:

await db.query(sql, [])

You should check your code again, and make sure you do not pass it an empty array, as it would try to format the query, and correctly throw that error.

I seem to have found the problem...

[vitaly-t]

@dmfay There seems to be a problem with your implementation of method query:

Database.prototype.query = function (query, params = [], options = {}) {

You cannot default formatting parameters params to [], you have to default it to undefined. That's how pg-promise works. If it finds [], it runs format on the query, for the syntax of $1, $2, ..., and if it finds any such variable, it tries to replace those. And because you default it incorrectly, any query with text that looks like those variables will result in that RangeError, as reported.

I'm a bit surprised this didn't come up earlier. This is how pg-promise worked always, b.t.w.

@IsaiahJTurner As a work-around for now, it will work, if you execute it through pg-promise driver directly:

await db.instance.query(sql)

@dmfay Here're some query-formatting test cases, to give you an idea, what tests you might want to add, along with the code changes...

const format = require('pg-promise').as.format;

format('$1') // does not format
//=> $1

format('$1, $2') // does not format
//=> $1, $2

format('$1', null)
//=> null

format('$1', undefined) // does not format
//=> $1

format('$1', [undefined])
//=> null

format('$1, $2', 123)
//=> 123, $2

format('$1, $2', [])
//=> RangeError: Variable $1 out of range. Parameters array length: 0

format('$1, $2', [123])
//=> RangeError: Variable $2 out of range. Parameters array length: 1

format('$1, $2', [123, 456])
//=> 123, 456

[dmfay]

Fixed in 5.5.1.