pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

join against a function-result fails
2+ messages / 2 participants
[nested] [flat]

* join against a function-result fails
@ 2012-07-28 01:57  Andreas <maps.on@gmx.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Andreas @ 2012-07-28 01:57 UTC (permalink / raw)
  To: pgsql-sql

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?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: join against a function-result fails
@ 2012-07-28 02:31  David Johnston <polobo@yahoo.com>
  parent: Andreas <maps.on@gmx.net>
  0 siblings, 0 replies; 2+ messages in thread

From: David Johnston @ 2012-07-28 02:31 UTC (permalink / raw)
  To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql



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 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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-07-28 02:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-28 01:57 join against a function-result fails Andreas <maps.on@gmx.net>
2012-07-28 02:31 ` David Johnston <polobo@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox