From: Gurjeet Singh <singh.gurjeet@gmail.com>
To: Andreas <maps.on@gmx.net>
Cc: Filip Rembiałkowski <plk.zuber@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to cascade information like the user roles ?
Date: Wed, 20 Jan 2010 11:15:57 +0530
Message-ID: <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com> (raw)
In-Reply-To: <4B55E22F.80409@gmx.net>
References: <4B54FDF3.4070601@gmx.net>
<92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com>
<4B55E22F.80409@gmx.net>
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
Attachments:
App_role_inheritance.sql (application/octet-stream, 2.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: singh.gurjeet@gmail.com, maps.on@gmx.net, plk.zuber@gmail.com
Subject: Re: How to cascade information like the user roles ?
In-Reply-To: <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox