Re: Users and groups

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(dot)Elphick(at)lfix(dot)co(dot)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