select all groups a user belongs to in system tables

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