In PostgreSQL versions prior to 8.1, the functions to obtain group members is not present, so I wrote
a couple of simple ones to illustrate how to do this.
public.group_members(text) will return all members for a specific group.
public.group_members() will return all members for all groups.
Feel free to modify as you need. Enjoy!
## Returns all users of a specific group
CREATE OR REPLACE FUNCTION public.group_members(text)
RETURNS bool AS
$BODY$
DECLARE
v_group ALIAS FOR $1;
v_user TEXT;
v_user_id INT4;
v_good BOOL := FALSE;
v_grp_list INT4[];
v_ctr INT4 := 0;
v_size INT4;
BEGIN
SELECT grolist INTO v_grp_list
FROM pg_group
WHERE groname = v_group;
GET DIAGNOSTICS v_ctr = ROW_COUNT;
IF v_ctr = 0 THEN
RETURN FALSE;
END IF;
PERFORM set_config('client_min_messages', 'NOTICE', FALSE);
SELECT array_upper(v_grp_list, 1) INTO v_size;
WHILE v_ctr <= v_size LOOP
SELECT usename INTO v_user
FROM pg_user
WHERE usesysid = v_grp_list[v_ctr]
ORDER BY usename;
RAISE NOTICE '%', v_user;
v_ctr := v_ctr + 1;
END LOOP;
PERFORM set_config('client_min_messages', 'WARNING', FALSE);
RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
## ===========================================================================================
## Returns all users for all groups
CREATE OR REPLACE FUNCTION public.group_members()
RETURNS bool AS
$BODY$
DECLARE
v_group_id INT4;
v_group TEXT;
v_user TEXT;
v_user_id INT4;
v_good BOOL := FALSE;
v_grp_list INT4[];
v_ctr INT4 := 0;
v_size INT4;
v_msg TEXT;
v_group_csr CURSOR FOR SELECT groname, grosysid
FROM pg_group
ORDER BY groname;
BEGIN
PERFORM set_config('client_min_messages', 'NOTICE', FALSE);
OPEN v_group_csr;
LOOP
FETCH v_group_csr INTO v_group, v_group_id;
EXIT WHEN NOT FOUND;
GET DIAGNOSTICS v_ctr = ROW_COUNT;
IF v_ctr > 0 THEN
SELECT grolist INTO v_grp_list
FROM pg_group
WHERE groname = v_group;
SELECT array_upper(v_grp_list, 1) INTO v_size;
WHILE v_ctr <= v_size LOOP
SELECT usename, usesysid INTO v_user, v_user_id
FROM pg_user
WHERE usesysid = v_grp_list[v_ctr];
v_msg := 'Group: ' || v_group || ' GID: ' || v_group_id || ' User: ' || v_user || ' UID: ' || v_user_id;
RAISE NOTICE '%', v_msg;
v_ctr := v_ctr + 1;
END LOOP;
END IF;
END LOOP;
PERFORM set_config('client_min_messages', 'WARNING', FALSE);
RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Melvin Davidson
Database Developer
Computer & Communication Technologies, Inc.
6 Inverness Court East, Suite 220
Englewood, CO 80112
<<Melvin Davidson.vcf>>
BEGIN:VCARD VERSION:2.1 N:Davidson;Melvin FN:Melvin Davidson ORG:CCT TEL;WORK;VOICE:303-708-9228x305 ADR;WORK;ENCODING=QUOTED-PRINTABLE:;;6 Inverness Ct East=0D=0ASuite 220;Englewood;CO;80112;United States LABEL;WORK;ENCODING=QUOTED-PRINTABLE:6 Inverness Ct East=0D=0ASuite 220=0D=0AEnglewood, CO 80112=0D=0AUnited Stat= es EMAIL;PREF;INTERNET:mdavidson@xxxxxxxxx REV:20060518T221645Z END:VCARD