Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query

Question

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?

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

Answer 2

This thread is kind of old, but as I had some headache looking for an answer to the same question, here goes what I've found:

Even though the subquery returns an array, you have to tell Postgres it IS an array!

My test query had very much the same elements. The grolist of the pg_group view is an array of OIDs:

select * from pg_user
where usesysid = any (ARRAY(select grolist from pg_group where groname = 'test_group'))

The interesting thing is that the unnest function works on grolist as expected. This query returns just the same results:

select * from pg_user
where usesysid in (select UNNEST(grolist) from pg_group where groname = 'test_group')

Answer 3

Answer to question

How to replace that hard-coded array with an array dynamically retrieved?

You cannot replace it with a subquery because that triggers a different code path. Your attempt with:

WHERE person.favorite_number = ANY(SELECT hits ...)

... seems to make sense under the assumption that = ANY () expects an array. Which can be true. But it also accepts a set. See:

• IN vs ANY operator in PostgreSQL
• How to use ANY instead of IN in a WHERE clause?

When you put a subquery there, the construct resolves to a different code path expecting a set of elements, not an array. So you have to place your dynamically retrieved array without a subselect wrapper to even get there. Include the additional table event in the FROM clause somehow, then a plain column reference does the job. Like

SELECT p.* 
FROM   event e, person p
WHERE  e."when" = '2018-01-23'
AND    p.favorite_number = ANY(e.hits);

Or:

SELECT p.*
FROM   event  e
JOIN   person p ON p.favorite_number = ANY(e.hits)
WHERE  e."when" = '2018-01-23';

Answer to Luiz' answer

Even though the subquery returns an array, you have to tell Postgres it IS an array!

Well, it's not exactly like that. Postgres knows it's an array, your query just never gets to the code path that would expect an array, like explained above.

When you wrap the subquery in an ARRAY constructor, that produces a 2-dimenstional array. Try:

SELECT ARRAY(select grolist from pg_group where groname = 'test_group');

It also happens to make ANY expect an array again, since the subselect is now hidden behind the ARRAY constructor. And array dimensions are ignored by ANY. So your first query works, even if doing some extra work.

Your second query sticks with the subselect and adds unnest() to the mix, to actually produce the expected set of element values. Cheaper, less confusing, but still doing more work than necessary.

unnest(), of course, works as expected. Postgres always knew that grolist is an array. We also don't need to switch to IN, as ANY happily takes a set, too:

SELECT * FROM pg_user
WHERE  usesysid = ANY (SELECT unnest(grolist) FROM pg_group WHERE groname = 'test_group')

Simpler and faster:

SELECT u.*
FROM   pg_group g
JOIN   pg_user  u ON u.usesysid = ANY (g.grolist)
WHERE  g.groname = 'test_group';

ANY actually expects the array we are giving.

But you probably don't want to use pg_group at all. The manual:

The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles that are being used as groups.

Approximation! The concepts of "groups" and "users" have been replaced with just "roles" with Postgres 8.1 in 2005 (!). You can still have "group roles", created with NOLOGIN (reflected in the system column pg_roles.rolcanlogin), but "user roles" (created with LOGIN) can have members as well. And that's commonly used. pg_group is only still there for backward compatibility and probably should be removed altogether. It does not include memberships in "user roles". The chapter "Database Roles" in the manual is a recommended read.

You probably want to target pg_auth_members instead

The catalog pg_auth_members shows the membership relations between roles.

And that can probably just be:

SELECT member::regrole
FROM   pg_auth_members
WHERE  roleid = 'test_group'::regrole;

See:

• How to get all roles that a user is a member of (including inherited roles)?
• How to revoke all group roles from login role