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