Passing Array as Values in PG-Promise only gets one result

Question

I'm trying to pass an array of items to a SELECT query and get all the rows back with those items.

So far I've got it to work using a join method, but I'd prefer to just pass the array and understand what I'm doing wrong. Perhaps I should just use the join method?

This works:

var values = ['WH12345678','WH22345678']
values = "'" + values.join("','") + "'";
var query = "select * from pups where workorder IN (" + values + ")"
db.any(query)

I can't get this to work, tried tons of variations:

Note: It does current "work" in that it return the first item row, but not both.

var values = ['WH12345678','WH22345678']
var values = ["WH12345678","WH22345678"]
var query = "select * from pups WHERE workorder IN ($1:list)"
var query = "select * from pups WHERE workorder IN ($1:csv)"
db.any(query,values)

Answer 1

From the docs of db.any:

the values can be:

• a single value - to replace all $1 occurrences
• an array of values - to replace all $1, $2, ... variables
• an object - to apply Named Parameters formatting

Since you passed an array (['WH12345678','WH22345678']), the second case applies: $1 expands to 'WH12345678' and $2 (which you're not using in your query) would expand to 'WH22345678'.

You need to pass an array instead that has your values array as its first element and no further elements for other parameters:

var values = ["WH12345678","WH22345678"];
var query = "select * from pups WHERE workorder IN ($1:list)";
db.any(query, [values])
//            ^      ^

Alternatively, pass an object to use named parameters:

var values = ["WH12345678","WH22345678"];
var query = "select * from pups WHERE workorder IN (${values:list})";
//                                                    ^^^^^^
db.any(query, {values})
//            ^      ^