Re: looking up members of a group
От | Joe Conway |
---|---|
Тема | Re: looking up members of a group |
Дата | |
Msg-id | 3E70A2B8.7030509@joeconway.com обсуждение исходный текст |
Ответ на | looking up members of a group (Michiel Lange <michiel@minas.demon.nl>) |
Список | pgsql-novice |
Michiel Lange wrote: > I am looking for a way to determine wether or not a user is a member of > a group... > > if I do: SELECT * FROM pg_group; > I get something like this: > groname | grosysid | grolist > ----------+----------+----------------------- > users | 103 | {100,102} > admins | 101 | {103,1} > guests | 100 | {101} > customer | 102 | {104,105,106,107,108} > > that's pretty ok, and it appears that the members of the group are in an > array... arrays in a database... it's always been a blast to me, always > having trouble working with them, so I don't use arrays often... but > here I must *grin* > > Anyway... I want to know if a user 'john' is member of 'admins', to > determine wether or not to show an administrative button on the website. In general, one good place to look for prewritten plpgsql is here: http://www.brasileiro.net:8080/postgres/cookbook/ You didn't mention your version, but in 7.3 and up this will work: 8<---------------------------------------------------------------------- CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename name); CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS ' DECLARE rec record; groview record; low int; high int; BEGIN FOR rec IN SELECT grosysid FROM pg_group LOOP SELECT INTO low replace(split_part(array_dims(grolist),'':'',1),''['','''')::int FROM pg_group WHERE grosysid = rec.grosysid; IF low IS NULL THEN low := 1; high := 1; ELSE SELECT INTO high replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int FROM pg_group WHERE grosysid = rec.grosysid; IF high IS NULL THEN high := 1; END IF; END IF; FOR i IN low..high LOOP SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i] WHERE grosysid = rec.grosysid; RETURN NEXT groview; END LOOP; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; CREATE VIEW groupview AS SELECT * FROM expand_groups(); 8<---------------------------------------------------------------------- regression=# SELECT * FROM groupview WHERE groname = 'admins'; grosysid | groname | usesysid | usename ----------+---------+----------+---------- 102 | admins | 1 | postgres 102 | admins | 103 | john (2 rows) See the manual for more on plpgsql: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql.html And there is a good article on table functions on techdocs: http://techdocs.postgresql.org/guides/SetReturningFunctions HTH, Joe
В списке pgsql-novice по дате отправления: