Functions to obtain group members- PostgreSQL prior to 8.1

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>>