You need a recursive lookup, and I guess 8.4's WITH RECURSIVE construct can help here. But if you are on an older version of Postgres, you will have to write recursive functions to do it.
I tried my hands on it, and attached is an implementation of such a recursive function. It returns the expected results. Hope it helps. Best regards, 2010/1/19 Andreas <maps...@gmx.net> > Filip Rembiałkowski schrieb: > >> >> >> 2010/1/19 Andreas <maps...@gmx.net <mailto:maps...@gmx.net>> >> >> >> Hi, >> >> I need something like the user-roles of PG to store options of my >> users. >> I guess i need a table with roles, options and one that stores the >> refernces from roles to options. >> >> roles (role_id, role_name) >> option (option_id, option_name) >> role_has_option (role_fk, option_fk) >> >> so far is easy. Now I can let role1 have option1 and option2 ... >> >> But I'd further like to let role2 inherit role1's options and also >> have option3. >> role_inherits_role (parent_role_fk, child_role_fk) >> 1, 2 >> >> What SELECT would deliver all options for role2 inkluding the >> inherited ones? >> like >> role_id, option_id >> 2, 1 >> 2, 2 >> 2, 3 >> >> >> >> select role_fk as role_id, option_fk as option_id from role_has_option >> where role_fk = 2 >> union >> select inh.child_role_fk, opt.option_fk from role_has_option opt join >> role_inherits_role inh on inh.parent_role_fk = opt.role_fk where >> inh.child_role_fk = 2 >> > Thanks. > I am looking for a more general solution that expands even multiple steps > of inheritance like a more complex example: > role_1 --> option_1 + option_2 > role_2 --> option_3 and inherits role_1 > role_3 --> option_2 + option_4 > > role_4 --> option_5 and inherits role_2 and role_3 > > I need a general solution that gives all options for any given role > including every inherited options over a unlimited hierarchy of parents. > Sounds complex, I know, but this is what PG does with its user-roles. > So I'd do in this example a SELECT ... WHERE role_id = 4 > and get > 4, 5 directly > 4, 3 from role_2 > 4, 1 from role_1 over role_2 > 4, 2 from role_1 over role_2 > 4, 2 from role_3 (inherited double occurance) > 4, 4 from role_4 > > > > > > > > > > > > > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- gurjeet.singh @ EnterpriseDB - The Enterprise Postgres Company http://www.enterprisedb.com singh.gurj...@{ gmail | yahoo }.com Twitter/Skype: singh_gurjeet Mail sent from my BlackLaptop device
App_role_inheritance.sql
Description: Binary data
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
