Message-ID: <501346EC.2000305@gmx.net>
Date: Sat, 28 Jul 2012 03:57:00 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:14.0) Gecko/20120713 Thunderbird/14.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: join against a function-result fails
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
I have a table with user ids and names.
Another table describes some rights of those users and still another one 
describes who inherits rights from who.

A function all_rights ( user_id ) calculates all rights of a user 
recursively and gives back a table with all userright_ids this user 
directly has or inherits of other users as ( user_id, userright_id ).

Now I'd like to find all users who have the right 42.


select  user_id, user_name
from    users
     join  all_rights ( user_id )  using ( user_id )
where  userright_id = 42;

won't work because the parameter user_id for the function all_rights() 
is unknown when the function gets called.

Is there a way to do this?