Re: pg_group view

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