A query like this:
SELECT * FROM person WHERE person.favorite_number = ANY( ARRAY[ 2 , 3 ] ) ;
ANY() function tells us if the single value is found within an array of values. So, we find any person with a favorite number of 2, or of 3, but omit people who chose their single favorite number to be 0, 1, 4, 5, or so on.
➥ How to replace that hard-coded array with an array dynamically retrieved?
Imagine a second table
event with a column
hits of type
INTEGER (an array of integers). I want to pull the one array from a single row and feed as the argument to
ANY( ) of the outer query.
Something like this:
SELECT * FROM person WHERE person.favorite_number = ANY( SELECT hits FROM event WHERE when = DATE '2018-01-23' ; -- Returns a single array from a table with `hits` column of type `INTEGER`. ) ;
SELECT hits FROM event WHERE when = DATE '2018-01-23' ; is what stumps me. How do I get a single array resulting from one query to be the argument to a function in the WHERE clause of another outer query?