I'm using NodeJS and pg with a PostgreSQL database as backend. Running single queries with Client.query works fine.
However, I've got a challenge which I can solve in a simple and elegant way.
I would like to run:
UPDATE portfolios SET votes = $1 WHERE id = $2
from an array/list of:
[{votes: 5, id: 1}, {votes: 15, id: 1}, {votes: 25, id: 2}]
Can it be done in a single Client.query (or similar with pg), so I don't have to make a "for () { ... }"?
The driver must pass the query to Postgresql in this final shape:
update portfolios p
set votes = s.votes
from unnest(array[(5, 1), (15, 1), (25, 2)]) s (votes int, id int)
where p.id = s.id
So pass this query to the driver:
update portfolios p
set votes = s.votes
from unnest($1) s (votes int, id int)
where p.id = s.id
with an appropriate array as parameter.
Notice that if you string build it yourself in instead of letting the driver do it you will be vulnerable to SQL injection.
