PostgreSQL - how build a where clause from nested array

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).

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), …] );

Create a PostgreSQL function that takes an array of user-defined tuples and uses the "unnest" function to compare values and construct the desired WHERE clause.

1. Create a user-defined type

CREATE TYPE file_idx_type AS (
    file text,
    idx smallint
);

2.After it, Create the PostgreSQL function like below:

CREATE OR REPLACE FUNCTION your_function_name(p_data file_idx_type[])
RETURNS TABLE (...) -- Replace with the appropriate return type
AS $$
BEGIN
    RETURN QUERY
    SELECT ...
    FROM ...
    WHERE project = 123 AND
    (file, fieldidx) IN (SELECT (file, idx) FROM unnest(p_data) AS t(file text, idx smallint));
END;
$$ LANGUAGE plpgsql;

This function, you can pass an array of "file_idx_type"
tuples as an argument,
and it will be used to construct the WHERE clause in your main query without using UNION.

I hope this gonna work..

#Apache-Age #postgrsql #psql