Kurt wrote:
> I'm trying to extract all the groups to which a user belongs from
> pg_group.
If you're using 7.3.x, this should work:
CREATE 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' STABLE STRICT;
CREATE VIEW groupview AS SELECT * FROM expand_groups();
regression=# select * from expand_groups();
grosysid | groname | usesysid | usename
----------+---------+----------+---------
100 | g1 | 100 | user1
100 | g1 | 101 | user2
101 | g2 | 100 | user1
101 | g2 | 101 | user2
101 | g2 | 102 | user3
(5 rows)
regression=# select groname from expand_groups() where usename = 'user1';
groname
---------
g1
g2
(2 rows)
regression=# select groname from expand_groups() where usename = 'user3';
groname
---------
g2
(1 row)
This will hopefully be easier in 7.4 (patch submitted but not yet applied):
regression=# SELECT g.groname FROM pg_shadow s, pg_group g WHERE
s.usesysid = any (g.grolist) and s.usename = 'user1';
groname
---------
g1
g2
(2 rows)
regression=# SELECT g.groname FROM pg_shadow s, pg_group g WHERE
s.usesysid = any (g.grolist) and s.usename = 'user3';
groname
---------
g2
(1 row)
HTH,
Joe
