Yep, that's it!
---------------------------------------------------------------------------
Joe Conway wrote:
> Bruce Momjian wrote:
> > Allow easy display of usernames in a group (pg_hba.conf uses groups now)
>
> Hows this:
>
> parts=# select * from pg_group ;
> groname | grosysid | grolist
> ---------+----------+---------------
> grp | 100 | {100,101,102}
> grp2 | 101 | {102}
> (2 rows)
>
> parts=# select usename,usesysid from pg_user;
> usename | usesysid
> ----------+----------
> postgres | 1
> user1 | 100
> user2 | 101
> user3 | 102
> (4 rows)
>
> CREATE FUNCTION show_group(text) RETURNS SETOF text AS '
> DECLARE
> loginname text;
> low int;
> high int;
> BEGIN
> SELECT INTO low
> replace(split(array_dims(grolist),'':'',1),''['','''')::int
> FROM pg_group WHERE groname = $1;
> SELECT INTO high
> replace(split(array_dims(grolist),'':'',2),'']'','''')::int
> FROM pg_group WHERE groname = $1;
>
> FOR i IN low..high LOOP
> SELECT INTO loginname s.usename
> FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i];
> RETURN NEXT loginname;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
> parts=# select * from show_group('grp');
> show_group
> ------------
> user1
> user2
> user3
> (3 rows)
>
> parts=# select * from show_group('grp2');
> show_group
> ------------
> user1
> (1 row)
>
>
> --Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
