Re: Removing all users from a group

Thanks Patrick and Bruno for your replies,

The auto-added "FROM" feature is pretty slick for enabling JOINs within a
DELETE. Allowing this to be explicit in 8.1 is going to be even better.

Since DELETEing from pg_users is an unsupported way to remove users, I am
going to use the procedure in the end. It's a little-modified version of
Patrick's code:

CREATE OR REPLACE FUNCTION removeUsersFromGroup( groupName name ) RETURNS
int4 AS $$
DECLARE
userRecord RECORD;
numUsersDropped int4 := 0;
BEGIN
FOR userRecord IN
SELECT usename FROM pg_user,pg_group
WHERE usesysid = ANY (grolist)
AND groname = groupName
LOOP
numUsersDropped := numUsersDropped + 1;
EXECUTE('DROP USER ' || userRecord.usename);
END LOOP;
RETURN numUsersDropped;
END
$$ LANGUAGE 'plpgsql';

Thanks again for the help,

David J. Sankel

On 8/31/05, David Sankel <camior(at)gmail(dot)com> wrote:
>
> 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
>