DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=jZZ4Ej6UTdukmauDj3T5IXhns1Wza6ePPwpDhouYCP3ZIP88zZZpylccAoZWnmRgdHJ5RqJAwrHlLMRE1CBUqFg71YQiUiLrf1PMqHE15V4YtuDbbhQgt3IZ1Qo5/CTbevL4sKKmsJQSxpabGJuRZJlfJABxR2Z53AVJxo+2yOE=
	;
References: <501346EC.2000305@gmx.net>
In-Reply-To: <501346EC.2000305@gmx.net>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <C919229E-2FF8-4BA8-BF91-266534861A1A@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: join against a function-result fails
Date: Fri, 27 Jul 2012 22:31:46 -0400
To: Andreas <maps.on@gmx.net>


On Jul 27, 2012, at 21:57, Andreas <maps.on@gmx.net> wrote
> Hi,
> I have a table with user ids and names.
> Another table describes some rights of those users and still another one d=
escribes who inherits rights from who.
>=20
> A function all_rights ( user_id ) calculates all rights of a user recursiv=
ely and gives back a table with all userright_ids this user directly has or i=
nherits of other users as ( user_id, userright_id ).
>=20
> Now I'd like to find all users who have the right 42.
>=20
>=20
> select  user_id, user_name
> from    users
>    join  all_rights ( user_id )  using ( user_id )
> where  userright_id =3D 42;
>=20
> won't work because the parameter user_id for the function all_rights() is u=
nknown when the function gets called.
>=20
> Is there a way to do this?
>=20

Suggest you write a recursive query that does what you want.  If you really w=
ant to do it this way you can:

With cte as (Select user_id, user_name, all_rights(user_id) as rightstbl)
Select * from cte where (rightstbl).userright_id =3D 42;

This is going to be very inefficient since you enumerate every right for eve=
ry user before applying the filter.  With a recursive CTE you can start at t=
he bottom of the trees and only evaluate the needed branches.

David J.