PostgreSQL + JS: Creating a query based on a random array length of 1-5 values

Question

I have a function that returns an Array of 1-5 integers, each time the array length is different:

[5, 10, 45, 55, 64]

How would I generate a SQL query based on the length of that array?

Example: if the array is 3 integers long I would need a query such as

Array: [10, 25, 30]

SELECT * FROM comments WHERE id IN($1, $2, $3)

if the array is 5 integers long I would need:

Array: [5, 9, 20, 41, 35]

SELECT * FROM comments WHERE id IN($1, $2, $3, &4, &5)

I think this is a duplicate of https://stackoverflow.com/questions/10720420/node-postgres-how-to-execute-where-col-in-dynamic-value-list-query. basically just join the params with ',' for the in clause. — Mike, May 28 '19 at 21:37
much thanks for the link, I couldn't figure out how to word my question so google would return the correct question answer — DIRTY DAVE, May 28 '19 at 21:41

demircioglu · Accepted Answer · 2019-05-28T23:42:23.960

0

Build IN part in a variable and attach it to the SQL

If your array has [10, 25, 30]

build a variable with v = "$1, $2, $3"

if the array has [5, 9, 20, 41, 35]

build the variable with v = "$1, $2, $3, $4, $5"

Then attach it to the SQL

SQL = 'SELECT * FROM comments WHERE id IN(v)'

edited May 28 '19 at 23:42

answered May 28 '19 at 21:39

demircioglu

1 Answers1