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?

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Jul 27, 2012, at 21:57, Andreas <maps.on (AT) gmx (DOT) net> wrote
Suggest you write a recursive query that does what you want. If you reallywant 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 atthe bottom of the trees and only evaluate the needed branches.

David J.


--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql