mapping user name to group name

On Monday 10 Mar 2003 5:47 pm, Matthew Phillips wrote:

Hey guys... first time caller, long time listener.

Don't ask me why, but I would like a quick way to map a user name to the
group name that the user is part of. Using pg_user and pg_group makes
this a little tricky. You can obtain usesysid from pg_user, and then get
the groname from pg_group based on it. However each group has its own
usesysids within an array of integers. I have no clue how to search
within an array using a select statement.

By default - you don't.

Am I making it harder than it should be? Is there a sys table already
out there that is doing this mapping for me? How do I do this (with
minimal hastle)

Nope - PG is making it harder than it should be. Having said that there are
some array-handling functions in contrib/intarray (sorry - don't know what,
never used them).

Also, I'm sure this must have been dealt with before. I'd recommend checking
techdocs.postgresql.org and especially Roberto's plpgsql cookbook. If that
comes up with nothing, it shouldn't be too difficult to write a small plpgsql
function to do what you want. If you're not familiar with plpgsql get back to
me and I'll run something up for you.

--
Richard Huxton

On Monday 10 Mar 2003 5:47 pm, Matthew Phillips wrote:

to map a user name to
the group name
has its own usesysids within an array of integers. I have no clue how
to search within an array using a select statement.

there
are some array-handling functions in contrib/intarray

SELECT
pg_group.groname,
pg_user.usename
FROM pg_user, pg_group
WHERE (pg_group.grolist *= pg_user.usesysid)
ORDER BY pg_group.groname, pg_user.usename;

works for me, after installing the array-handing functions referred to by
Huxton.

~Berend Tober