Forum OpenACS Q&A: Postgres syntax requires me to run a function repeatedly?
If you are SELECTing a function and then also using it in a WHERE statement, is it possible to avoid having to calculate the result of the function twice?
I'm building off of the bboard search function in openACS 3.2.5 to do some searching of a hacked-together-module to archive experiments. (www.labarchive.net/labdb/search). I'm using the rank_for_search function defined in rank_for_search.sql. I want to only return hits with a rating greater than 50% (or whatever). The present code does a select with the rank_for_search function, and then also has a WHERE statement further down with the same rank_for_search function. It seems to me that this means the rank score is being calculated twice. I tried selecting rank_for_search(args) AS rank and then doing a WHERE rank > 50, but that gives an error message. (Attribute 'rank' not found.)
Can some postgres guru give me some advice? It seems inefficient to be calculating the score twice for each entry. Is there a better way to do this?
Thanks in advance,
select * from (select *, rank_for_search('david'::varchar,first_names) as rank from users) qry_alias where rank > 50 order by rank desc
IsCacheable (However it is spelled) is certainly the way to go with the rank_for_search function. The same arguments passed in will always produce the same result.
select a, func(b) from table order by 1, 2At least you can in Oracle...
email || first_names || last_name || one_line || message::text) as the_score,
case when bboard.refers_to is null then 1 else 0 end,
bboard.*, users.first_names || ' ' || users.last_name as name, users.email, bboard_topics.presentation_type, bboard_topics.topic
from bboard, users, bboard_topics
where rank_for_search('$final_query_string', email || first_names || last_name || one_line || message::text) > 0
and bboard.user_id = users.user_id
and (active_p = 't' or active_p is null)
and (bboard_topics.moderation_policy is null or bboard_topics.moderation_policy != 'private')
and bboard_topics.topic_id = bboard.topic_id
order by 1 desc, 2 desc
but it is called in the WHERE clause. As you can see this is a horrible query no matter how you look at it as it does a sequential scan on the entire table. Well, not in this case which is searching a single topic (forum), but it does in the "search all forums" variant.
But at least it only calls the rank_for_search function twice rather than three times per returned row, and of course only once for each rejected row!