Обсуждение: pg_group view

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

Instead of:

groname | grolist
------------------
1       | {1:2}
2       | {1}

--
Robert Abernethy IV
Dynamic Edge, Inc.
734.975.0460

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

On Mon, 2002-12-30 at 22:16, Joe Conway wrote:
> 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?
...
> 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';

Without the extra WHERE clause, the wrong group is shown where a user is
a member of more than one group.

--
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
                 ========================================
     "Ye have heard that it hath been said, Thou shalt love
      thy neighbour, and hate thine enemy. But I say unto
      you, Love your enemies, bless them that curse you, do
      good to them that hate you, and pray for them which
      despitefully use you, and persecute you;"
                                     Matthew 5:43,44

Oliver Elphick wrote:
>            WHERE grosysid = rec.grosysid;
>

Thanks, nice catch!

You exposed my lack of adequate testing. I guess I should have added a
disclaimer ;-)

Joe