Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 3A3EA6326B5 for ; Wed, 20 Jan 2010 02:24:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 69367-08 for ; Wed, 20 Jan 2010 06:24:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f190.google.com (mail-qy0-f190.google.com [209.85.221.190]) by mail.postgresql.org (Postfix) with ESMTP id 7E828632403 for ; Wed, 20 Jan 2010 02:24:01 -0400 (AST) Received: by qyk28 with SMTP id 28so3060069qyk.28 for ; Tue, 19 Jan 2010 22:24:00 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=UmLzrq5UcO28JzjjNprVLB7yusRHZWQW3U3N32EIAFE=; b=F359xoqSgHaQf6UlA2MLeqx0eCGopguyF/Qpz0PZmpLb7G/W0VEH3I7l5gGhoefKyV EOuwiaCotXgLUfAp5K9j1mXSLMrWEnJf0jb57E5Kw8wzySoKwJ0liO5Hrv63JHbjCTCC WkACB8L3OvTJSxYaU0n6UvrFgY7qv07ZpXbEQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=GRxRtOYjdXDVYWG2uKMxapH1WYmvQy9Hkfei2/Le3RXSb28+ZxJ3m06BFf9bzk6x/J rvU0v6HQkJ3j77wZm5syzooVtMHzrywCebVr6cIm6DDIHAQtuIahXcF4F3rhFaH3+tbK DWw52zWHq69IFfUeDxnM8cTXeTERzvRGyepJ4= MIME-Version: 1.0 Received: by 10.220.122.205 with SMTP id m13mr242078vcr.112.1263968640186; Tue, 19 Jan 2010 22:24:00 -0800 (PST) In-Reply-To: <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com> References: <4B54FDF3.4070601@gmx.net> <92869e661001190627i4d42e368nbb0e96bf5c54ca76@mail.gmail.com> <4B55E22F.80409@gmx.net> <65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com> From: Gurjeet Singh Date: Wed, 20 Jan 2010 11:53:39 +0530 Message-ID: <65937bea1001192223o2a443c3dw9148352fc2bb76d4@mail.gmail.com> Subject: Re: How to cascade information like the user roles ? To: Andreas Cc: =?ISO-8859-2?Q?Filip_Rembia=B3kowski?= , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e68f9f78ce5e88047d92a107 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/38 X-Sequence-Number: 33574 --0016e68f9f78ce5e88047d92a107 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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=3D# 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 =3D r.role_id) select /* r.role_id, */ m.option_id from roles as r, app_role_option_map m where m.role_id =3D 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 wr= ote: > You need a recursive lookup, and I guess 8.4's WITH RECURSIVE construct c= an > 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 > >> Filip Rembia=C5=82kowski schrieb: >> >>> >>> >>> 2010/1/19 Andreas > >>> >>> >>> 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 =3D 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 =3D opt.role_fk where >>> inh.child_role_fk =3D 2 >>> >> Thanks. >> I am looking for a more general solution that expands even multiple step= s >> 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 =3D 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 > --=20 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 --0016e68f9f78ce5e88047d92a107 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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=3D# wi= th recursive roles(role_id) as
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 (select 4
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2= =A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 union all
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 se= lect parent_role_id
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 fr= om app_role_inherits as i,
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 = =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 roles as r
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2= =A0=C2=A0 =C2=A0=C2=A0=C2=A0 where i.role_id =3D r.role_id)
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 select /* r.role_id, */ m.option_id
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0= =C2=A0=C2=A0 from roles as r,
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 ap= p_role_option_map m
=C2=A0=C2=A0=C2=A0 =C2= =A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 where m.role_id =3D r.role_id; =C2=A0option_id =
-----------
=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 1
=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2
=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 3
=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2
=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 4
=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 5
(6 rows)

Best regards,

On Wed, Jan 20, 2010 at 11:15 AM, Gurjeet Singh <singh.gurjeet@gmai= l.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 ha= ve to write recursive functions to do it.

I tried my hands on it, and attached is an implementation of such a rec= ursive function. It returns the expected results.

Hope it helps.

Best regards,

2010/1/19 Andreas <maps.on@gmx.net><= /span>
Filip Rembia=C5=82kowski schrieb:


2010/1/19 Andreas <= maps.on@gmx.net <mailto:maps.on@gmx.net>>


=C2=A0 =C2=A0Hi,

=C2=A0 =C2=A0I need something like the user-roles of PG to store options o= f my
=C2=A0 =C2=A0users.
=C2=A0 =C2=A0I guess i need a table with roles, options and one that store= s the
=C2=A0 =C2=A0refernces from roles to options.

=C2=A0 =C2=A0roles (role_id, role_name)
=C2=A0 =C2=A0option (option_id, option_name)
=C2=A0 =C2=A0role_has_option (role_fk, option_fk)

=C2=A0 =C2=A0so far is easy. Now I can let role1 have option1 and option2 = ...

=C2=A0 =C2=A0But I'd further like to let role2 inherit role1's opt= ions and also
=C2=A0 =C2=A0have option3.
=C2=A0 =C2=A0role_inherits_role (parent_role_fk, child_role_fk)
=C2=A0 =C2=A01, 2

=C2=A0 =C2=A0What SELECT would deliver all options for role2 inkluding the=
=C2=A0 =C2=A0inherited ones?
=C2=A0 =C2=A0like
=C2=A0 =C2=A0role_id, option_id
=C2=A0 =C2=A02, 1
=C2=A0 =C2=A02, 2
=C2=A0 =C2=A02, 3



select role_fk as role_id, option_fk as option_id from role_has_option wher= e role_fk =3D 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 =3D opt.role_fk where inh.child_rol= e_fk =3D 2
Thanks.
I am looking for a more general solution that expands even multiple steps o= f inheritance like a more complex example:
role_1 =C2=A0--> =C2=A0option_1 + option_2
role_2 =C2=A0--> =C2=A0option_3 and inherits role_1
role_3 =C2=A0--> =C2=A0option_2 + option_4

role_4 =C2=A0--> =C2=A0option_5 and inherits role_2 and role_3

I need a general solution that gives all options for any given role includi= ng 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 =3D 4
and get
4, 5 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 directly
4, 3 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_2 4, 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_1 ov= er role_2
4, 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_1 ov= er role_2
4, 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 from role_3 (i= nherited double occurance)
4, 4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 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.enterprised= b.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device


--
gurjeet.singh
@ Ente= rpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh.gurjeet@{ gmail | y= ahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device --0016e68f9f78ce5e88047d92a107--