Question 223327 in PostgreSQL Question posted in
The official documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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), …] );
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search