Re: select all groups a user belongs to in system tablesl

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 :\