From: Oliver Elphick <olly@lfix.co.uk>
To: Teddy <epalo@ono.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: Users and groups
Date: 01 Mar 2003 13:02:01 +0000
Message-ID: <1046523721.2173.20.camel@linda.lfix.co.uk> (raw)
In-Reply-To: <b369r4$2vd2$1@news.hub.org>
References: <b369r4$2vd2$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, epalo@ono.com, pgsql-admin@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Users and groups
In-Reply-To: <1046523721.2173.20.camel@linda.lfix.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox