Re: Users and groups - Mailing list pgsql-admin

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