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 [EMAIL PROTECTED]
