Postgresl : Loop a list of values to create SELECT with mulitple WHERE clause-CodePudding

I have a simple SQL query such as

SELECT * from tableName
WHERE (field = 'value1') OR (field = 'value2') OR (field = 'value3') ...

I am lazy enough to write hardcode write the SQL query like above. I want to extract value1, value2 , ... in an array ( or any collections)

How to write the query above so that it can loop the list of values ?

# declare array

# loop for each element in the array
SELECT * from tableName WHERE (field = element) 

# or even better
# build the WHERE clause with a loop
whereClause = (field = 'value1') OR (field = 'value2') OR (field = 'value3') ...

SELECT * from tableName WHERE whereClause

CodePudding user response：

As a_horse_with_no_name has mentioned you can

Check if the field is in an array

SELECT *
FROM   tableName 
WHERE  field = ANY(ARRAY['value1', 'value2'])

and you can use a language of your choice to fill these values dynamically

or if those values are already in a table you can

SELECT * 
FROM   tableName
WHERE  field IN (SELECT field FROM otherTableName)