Hello List,
I'm trying to delete all the users from a group and it seems as though there isn't sufficient array functionality to do it.
The pg_group columns:
http://www.postgresql.org/docs/8.0/interactive/catalog-pg-group.html The pg_user columns:
http://www.postgresql.org/docs/8.0/interactive/view-pg-user.html After having a peek at the above, we know we can see all the users in a group with this:
SELECT *
FROM pg_user, pg_group
WHERE usesysid = ANY (grolist)
AND groname = 'somegroupname';
"ANY" is a function that can tell if a value is in an array:
http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491 Okay, that select function worked fine, but if we want to delete we cannot use a join (implicit by the ',') to help us out. So the following should work:
DELETE FROM pg_user
WHERE usesysid = ANY ( SELECT grolist
FROM pg_group
WHERE groname = 'somegroupname' )
But, alas, it doesn't. Neither does any combination of IN and ANY. It seems to me like this should work since the same syntax works if we weren't talking about arrays.
So, how can we delete all users within a specified group? Is there a bug or is the above code incorrect?
When testing the above delete function, I found it useful to substitute "SELECT *" for "DELETE" to get non-destructive queries.
Thanks for any help,
David J. Sankel