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:
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
FROM event e, person p
WHERE e."when" = '2018-01-23'
AND p.favorite_number = ANY(e.hits);
FROM event e
JOIN person p ON p.favorite_number = ANY(e.hits)
WHERE e."when" = '2018-01-23';
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
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:
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:
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
which is an approximation to the set of roles that are being used as
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 shows the membership relations between roles.
And that can probably just be:
WHERE roleid = 'test_group'::regrole;