Re: Users and groups - Mailing list pgsql-sql

>
> How can i list the users of a group?
>
This is taken from a former thread.
You'll have to adapt it.

Regards, Christoph

From: Michiel Lange <michiel@minas.demon.nl>
To: Joe Conway <mail@joeconway.com>
Subject: Re: [SQL] Could someone help me fix my array_list function?
Cc: Guy Fraser <guy@incentre.net>, pgsql-sql@postgresql.org
Date: Tue, 21 Jan 2003 01:38:13 +0100

Would the same work for pg_user and pg_group?

It would be handy at times to easily check wether or not someone is
member
of a group...
and since in pg_group the usernumbers are stored, one might need to do a

few lookups:
would it be hard to put such a thing in a view, or is that not-smart
thinking here?

I have to admit, arrays are still a bit hazy to me, in how to use them
properly in databases...
so I stick to the older solutions...

Michiel

At 15:27 20-1-2003 -0800, Joe Conway wrote:
>Guy Fraser wrote:
>>This is what I want to do:
>>select attribute,array_list(values,1,sizeof(values)) as value from
av_list;
>>Turn :
>>  attr6 | {val3,val7,val4,val5}
>>Into :
>>  attr6 | val3
>>  attr6 | val7
>>  attr6 | val4
>>  attr6 | val5
>
>You didn't mention the version of PostgreSQL. If you're using < 7.3,
good
>luck ;-). If you are using 7.3, the following works:
>
>DROP TABLE mail_aliases;
>CREATE TABLE mail_aliases(
>   a_mailbox text,
>   a_destination text[]
>);
>
>INSERT INTO mail_aliases VALUES ('alias1', '{dest1}');
>INSERT INTO mail_aliases VALUES ('alias2', '{dest2,dest1}');
>INSERT INTO mail_aliases VALUES ('alias3', '{dest3,dest4}');
>INSERT INTO mail_aliases VALUES ('alias4', '{dest3,dest4,dest5}');
>INSERT INTO mail_aliases VALUES ('alias5', '{dest6,dest7}');
>INSERT INTO mail_aliases VALUES ('alias6',
'{dest3,dest7,dest4,dest5}');
>
>CREATE TYPE mail_aliases_list_type AS (a_mailbox text, a_destination_el
text);
>CREATE OR REPLACE FUNCTION mail_aliases_list() RETURNS SETOF
>mail_aliases_list_type AS '
>DECLARE
>   rec record;
>   retrec record;
>   low int;
>   high int;
>BEGIN
>  FOR rec IN SELECT a_mailbox, a_destination FROM mail_aliases LOOP
>   SELECT INTO low
>
>replace(split_part(array_dims(rec.a_destination),'':'',1),''['','''')::int;

>   SELECT INTO high
>
>replace(split_part(array_dims(rec.a_destination),'':'',2),'']'','''')::int;

>
>   FOR i IN low..high LOOP
>    SELECT INTO retrec rec.a_mailbox, rec.a_destination[i];
>    RETURN NEXT retrec;
>   END LOOP;
>  END LOOP;
>  RETURN;
>END;
>' LANGUAGE 'plpgsql';
>
>regression=# SELECT a_mailbox, a_destination_el FROM
mail_aliases_list();
>  a_mailbox | a_destination_el
>-----------+------------------
>  alias1    | dest1
>  alias2    | dest2
>  alias2    | dest1
>  alias3    | dest3
>  alias3    | dest4
>  alias4    | dest3
>  alias4    | dest4
>  alias4    | dest5
>  alias5    | dest6
>  alias5    | dest7
>  alias6    | dest3
>  alias6    | dest7
>  alias6    | dest4
>  alias6    | dest5
>(14 rows)
>
>
>HTH,
>
>Joe
>
From: Joe Conway <mail@joeconway.com>
To: Michiel Lange <michiel@minas.demon.nl>
Cc: Guy Fraser <guy@incentre.net>, pgsql-sql@postgresql.org
Subject: Re: [SQL] Could someone help me fix my array_list function?
Date: Mon, 20 Jan 2003 17:45:44 -0800

Michiel Lange wrote:
> Would the same work for pg_user and pg_group?
>

See:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378

With these groups:
regression=# select * from pg_group; groname | grosysid |    grolist
---------+----------+--------------- grp1    |      100 | {100,101,102} grp2    |      101 | {100,102}
(2 rows)

Output looks like:
regression=# select * from groupview; grosysid | groname | usesysid | usename
----------+---------+----------+---------      100 | grp1    |      100 | user1      100 | grp1    |      101 | user2
  100 | grp1    |      102 | user3      101 | grp2    |      100 | user1      101 | grp2    |      102 | user3
 
(5 rows)

Joe