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(dot)Elphick(at)lfix(dot)co(dot)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