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
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';
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: