From: David Johnston <polobo@yahoo.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: join against a function-result fails
Date: Fri, 27 Jul 2012 22:31:46 -0400
Message-ID: <C919229E-2FF8-4BA8-BF91-266534861A1A@yahoo.com> (raw)
In-Reply-To: <501346EC.2000305@gmx.net>
References: <501346EC.2000305@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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, maps.on@gmx.net
Subject: Re: join against a function-result fails
In-Reply-To: <C919229E-2FF8-4BA8-BF91-266534861A1A@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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