Let's imagine we have a table containing columns for 'color' and for 'size'. I have a list with color-size combinations (e.g. [(red, small), (blue, medium)]), of which the length is unknown. The table should be filtered based on this list, so that the result contains only the rows where the combinations apply.
A query based on the example would look like this:
SELECT * FROM items WHERE ((color = 'red' AND size = 'small') OR (color = 'blue' and size = 'medium'));
Parameterizing this query wouldn't work of course, since the amount of combinations varies.
Is there a way to achieve this using the parameterized queries like the ones that are use in node-postgres? The only solution I can think of is using string interpolation, which doesn't appear to be a safe.
It looks like good scenario for IN operator
select * from items where
(color, size) in (('red','small'), ('blue','medium'))
and it can be parametrized using arrays
select * from items where
(color, size) in (
select unnest (array['red','blue']), unnest(array['small','medium']))
First array is for colors, second for sizes. Unnest in one select create pairs.Arrays should have the same number of elements.
And arrays can be passed as parameters to query.