Re: How to cascade information like the user roles ? - Mailing list pgsql-sql

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.on@gmx.net>

Filip Rembiałkowski schrieb:

2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device