Re: pg_group view

Rob Abernethy IV wrote:
> Does anyone have a good recipe for a view that will display users/gruops in a
> way that can be used with Tomcat's JDBCRelam configuration?  In other words,
> instead of seeing a *list* of users in a group (grolist), I'd like to see a
> separate row for every group/user combination.
>
> group | user
> -------------
> 1     | 1
> 1     | 2
> 2     | 1

You didn't say what version of PostgreSQL you're using. If it is prior to
7.3.x, you're pretty much out of luck. Starting with 7.3, you can use
something like the following:

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];
       RETURN NEXT groview;
     END LOOP;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE VIEW public.pg_groupview AS SELECT * FROM expand_groups();

test=# select * from pg_groupview;
  grosysid | groname | usesysid | usename
----------+---------+----------+---------
       100 | g1      |      100 | user1
       100 | g1      |      101 | user2
       100 | g1      |      100 | user1
       100 | g1      |      101 | user2
       101 | g2      |      102 | user3
(5 rows)

There *might* be a builtin function or view in 7.4 to do the same thing.

HTH,

Joe