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 [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])