Re: Users and groups
От | Christoph Haller |
---|---|
Тема | Re: Users and groups |
Дата | |
Msg-id | 3E63274A.3837B6C4@rodos.fzk.de обсуждение исходный текст |
Ответ на | Users and groups ("Teddy" <epalo@ono.com>) |
Список | 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
В списке pgsql-sql по дате отправления: