On Jul 27, 2012, at 21:57, Andreas <maps...@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 > 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? >
Suggest you write a recursive query that does what you want. If you really want 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 = 42; This is going to be very inefficient since you enumerate every right for every user before applying the filter. With a recursive CTE you can start at the bottom of the trees and only evaluate the needed branches. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
