0

A query like this:

SELECT * 
FROM person
WHERE person.favorite_number = ANY( 
    ARRAY[ 2 , 3 ] 
)
;

…runs. The 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[]`.
)
;

That 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?

1

You're pretty close. The only thing you're missing is unnest() to turn the array into a set-valued function, allowing it to work on the right-hand-side of an ANY.

testdb=# create table person(favorite_number bigint);
CREATE TABLE
testdb=# create table event("when" date, hits bigint[]);
CREATE TABLE
testdb=# insert into event select '2018-01-23', '{2,3}';
INSERT 0 1
testdb=# insert into person select 2;
INSERT 0 1
testdb=# select * from person where favorite_number=ANY(select hit from event, unnest(hits) AS hit where "when"='2018-01-23');
 favorite_number 
-----------------
               2
(1 row)
  • 1
    I'm confused. The unnest function is documented as expanding an array into a set of rows. But the ANY function takes an array expression, not a set of rows, as its argument. So how can this work? – Basil Bourque Nov 5 '18 at 21:39
  • ANY does not only work with an array expression - it can also use a single-column subquery, per the docs linked in the answer. – AdamKG Nov 6 '18 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.