Re: How to cascade information like the user roles ?

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