On Tue, Mar 1, 2011 at 2:51 AM, Orhan Kavrakoglu <orhan@xxxxxxxxxxx> 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 you missed the point: even when you use subqueries postgres can inline them, 'unsubquerying' your query. I think the OP nailed probably the best and most logical approach -- use a CTE. It's more formal, and while not super efficient today, isn't terrible. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
