X-Original-To: pgsql-admin@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id 19E4A475ADD; Sat,  1 Mar 2003 08:18:17 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 18164-02; Sat,  1 Mar 2003 08:18:16 -0500 (EST)
Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net
	[194.217.242.80]) by postgresql.org (Postfix) with ESMTP
	id DB383475BEC; Sat,  1 Mar 2003 08:01:59 -0500 (EST)
Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk)
	by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2)
	id 18p6cs-0002lK-0d; Sat, 01 Mar 2003 13:02:02 +0000
Received: from localhost ([127.0.0.1] ident=olly)
	by linda.lfix.co.uk with esmtp (Exim 3.36 #1 (Debian))
	id 18p6cr-00039j-00; Sat, 01 Mar 2003 13:02:01 +0000
Subject: Re: Users and groups
From: Oliver Elphick <olly@lfix.co.uk>
To: Teddy <epalo@ono.com>
Cc: pgsql-admin@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
In-Reply-To: <b369r4$2vd2$1@news.hub.org>
References: <b369r4$2vd2$1@news.hub.org>
Content-Type: text/plain
Organization: LFIX Limited
Message-Id: <1046523721.2173.20.camel@linda.lfix.co.uk>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.2 
Date: 01 Mar 2003 13:02:01 +0000
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/1
X-Sequence-Number: 7649

On Fri, 2003-02-21 at 22:37, Teddy wrote:
> How can i list the users of a group?

Someone posted this function a while back.  (It's a multi-row function,
so it needs 7.3):


CREATE OR REPLACE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename name);
                                                                                                                              
CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
  rec record;
  groview record;
  low int;
  high int;
BEGIN
  FOR rec IN SELECT grosysid FROM pg_group LOOP
    SELECT INTO low
      replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
      FROM pg_group WHERE grosysid = rec.grosysid;
    SELECT INTO high
      replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
      FROM pg_group WHERE grosysid = rec.grosysid;
                                                                                                                              
    FOR i IN low..high LOOP
      SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
        FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
        WHERE grosysid = rec.grosysid;
      RETURN NEXT groview;
    END LOOP;
  END LOOP;
  RETURN;
END;
' LANGUAGE 'plpgsql';
                                                                                                                              
CREATE OR REPLACE VIEW public.pg_groupview AS SELECT * FROM expand_groups();
                                                                                                                              


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "The LORD is my shepherd; I shall not want. He maketh 
      me to lie down in green pastures: he leadeth me beside
      the still waters, he restoreth my soul...Surely
      goodness and mercy shall follow me all the days of my
      life; and I will dwell in the house of the LORD for
      ever."                    Psalms 23:1,2,6