Re: How to cascade information like the user roles ?

And here's the WITH RECURSIVE version, which does not need the recursive
function, but will work only with Postgres 8.4 or above.

postgres=# with recursive roles(role_id) as
(select 4
union all
select parent_role_id
from app_role_inherits as i,
roles as r
where i.role_id = r.role_id)
select /* r.role_id, */ m.option_id
from roles as r,
app_role_option_map m
where m.role_id = r.role_id;
option_id
-----------
1
2
3
2
4
5
(6 rows)

Best regards,

On Wed, Jan 20, 2010 at 11:15 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>wrote:

> 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(dot)on(at)gmx(dot)net>
>
>> Filip Rembiałkowski schrieb:
>>
>>>
>>>
>>> 2010/1/19 Andreas <maps(dot)on(at)gmx(dot)net <mailto:maps(dot)on(at)gmx(dot)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(at)postgresql(dot)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(dot)gurjeet(at){ gmail | yahoo }.com
> Twitter/Skype: singh_gurjeet
>
> Mail sent from my BlackLaptop device
>

--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh(dot)gurjeet(at){ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device