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 the result 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(at)tart(dot)com(dot)tr

Tart New Media
w : http://www.tart.com.tr
t : +90 212 263 0 666 / ext: 142
f : TBA
a : TBA