Re: [SQL] FW: view derived from view doesn't use indexes
Hi Tom, Thanks for that, that was exactly the issue. It absolutely is IMMUTABLE and changing it from VOLATILE has fixed the issue. Much appreciated, Russell, -Original Message- From: Tom Lane [mailto:[email protected]] Sent: 26 July 2012 16:52 To: Russell Keane Cc: [email protected] Subject: Re: [SQL] FW: view derived from view doesn't use indexes Russell Keane writes: > Using PG 9.0 and given the following definitions: > CREATE OR REPLACE FUNCTION status_to_flag(status character) RETURNS > integer AS $BODY$ ... > $BODY$ > LANGUAGE plpgsql > CREATE OR REPLACE VIEW test_view1 AS > SELECT status_to_flag(test_table.status) AS flag, test_table.code_id > FROM test_table; > CREATE OR REPLACE VIEW test_view2 AS > SELECT * > FROM test_view1 > WHERE test_view1.flag = 1; I think the reason why the planner is afraid to flatten this is that the function is (by default) marked VOLATILE. Volatile functions in the select list are an optimization fence. That particular function looks like it should be IMMUTABLE instead, since it depends on no database state. If it does look at database state, you can probably use STABLE. http://www.postgresql.org/docs/9.0/static/xfunc-volatility.html regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] join against a function-result fails
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 fromusers 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] join against a function-result fails
On Jul 27, 2012, at 21:57, Andreas 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 > fromusers >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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
