PostgreSQL - how build a where clause from nested array

Question

I have a query:

SELECT …
FROM …
WHERE project = 123
AND (
    file = file1 and fieldidx = idx1
OR  file = file2 and fieldidx = idx2
OR  …)

… I want to create the where clause in a function that has the required array of tuples, where the tuple is a user type (file text, idx smallint).

I don’t know how (I’m trying to avoid using a union).

Answer 1

You don't need a function to do that. Just check whether the tuple is in the provided array:

SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) IN ((file1, idx1), (file2, idx2), …);

or

SELECT …
FROM …
WHERE project = 123
AND (file, fieldidx) = ANY( ARRAY[(file1, idx1), (file2, idx2), …] );