[GENERAL] Functions to obtain group members- PostgreSQL prior to 8.1

[Melvin Davidson]

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:[EMAIL PROTECTED]
REV:20060518T221645Z
END:VCARD

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq