Re: looking up members of a group

Michiel Lange wrote:
> I am looking for a way to determine wether or not a user is a member of
> a group...
>
> if I do: SELECT * FROM pg_group;
> I get something like this:
> groname | grosysid | grolist
> ----------+----------+-----------------------
> users | 103 | {100,102}
> admins | 101 | {103,1}
> guests | 100 | {101}
> customer | 102 | {104,105,106,107,108}
>
> that's pretty ok, and it appears that the members of the group are in an
> array... arrays in a database... it's always been a blast to me, always
> having trouble working with them, so I don't use arrays often... but
> here I must *grin*
>
> Anyway... I want to know if a user 'john' is member of 'admins', to
> determine wether or not to show an administrative button on the website.

In general, one good place to look for prewritten plpgsql is here:
http://www.brasileiro.net:8080/postgres/cookbook/

You didn't mention your version, but in 7.3 and up this will work:

8<----------------------------------------------------------------------
CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4,
usename name);

CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
rec record;
groview record;
low int;
high int;
BEGIN
FOR rec IN SELECT grosysid FROM pg_group LOOP
SELECT INTO low
replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
IF low IS NULL THEN
low := 1;
high := 1;
ELSE
SELECT INTO high
replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
FROM pg_group WHERE grosysid = rec.grosysid;
IF high IS NULL THEN
high := 1;
END IF;
END IF;

FOR i IN low..high LOOP
SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
WHERE grosysid = rec.grosysid;
RETURN NEXT groview;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE VIEW groupview AS SELECT * FROM expand_groups();
8<----------------------------------------------------------------------

regression=# SELECT * FROM groupview WHERE groname = 'admins';
grosysid | groname | usesysid | usename
----------+---------+----------+----------
102 | admins | 1 | postgres
102 | admins | 103 | john
(2 rows)

See the manual for more on plpgsql:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html

And there is a good article on table functions on techdocs:
http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH,

Joe