On 2011-02-03 18:07, Bob Price wrote:
> I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where
theresult is needed both as a returned value and as an expression in the WHERE clause.
I think I've seen it said here that PG avoids redundant multiple
calculations of an expression.
Even so, have you thought about using subqueries?
> SELECT id, expensivefunc(value) AS score FROM mytable
> WHERE id LIKE '%z%' AND expensivefunc(value)> 0.5;
SELECT id, expensivefunc(value) FROM (
(SELECT id, value FROM mytable WHERE id LIKE '%z%')
) WHERE expensivefunc(value) > 0.5;
or even
SELECT id, score FROM (
SELECT id, expensivefunc(value) AS score FROM (
(SELECT id, value FROM mytable WHERE id LIKE '%z%')
)
) WHERE score > 0.5
--
Orhan Kavrakoğlu
orhan@tart.com.tr
Tart New Media
w : http://www.tart.com.tr
t : +90 212 263 0 666 / ext: 142
f : TBA
a : TBA