Re: how to avoid repeating expensive computation in select

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