Re: select all groups a user belongs to in system tablesl - Mailing list pgsql-general

Uwe Feldtmann a écrit:
 > Hi,
 >
 > I'm new to sql and postgres and I am trying to create a query that will
 > return the list of groups a user belongs to.
 >
 > As the grolist column in pg_group is an array is there a simple way to
 > return the list?
 >
 > I've checked the array documentation and according to that document the
 > pg_group table should be redesigned.
 >
 > Any assistance would be welcome. Thanks in advanced.
 >

create a function in your database(s):

create function "seek_tab" (integer[],integer) returns boolean as '
    declare i int4;
    ufound boolean;
    begin
              i:=1;
              ufound:=false;
              while $1[i]<>0 and not ufound loop
                      if $1[i] = $2 then
                              ufound:=true;
                     end if;
                      i:=i+1;
              end loop;
              return ufound;
    end;
' LANGUAGE 'plpgsql';

------------
and the query to use it is:


select groname from pg_group where rech_tab(grolist,(select usesysid from pg_user where usename='foo'))=true ;


ps: I'm not a pl/pgsql guru :\