Re: how to avoid repeating expensive computation in select

Bob Price <rjp_email@yahoo.com> writes:
> 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. 

Use a subselect.  You might need OFFSET 0 to prevent the planner from
"flattening" the subselect, eg

    SELECT whatever FROM
      (SELECT *, expensivefunc(value) AS score FROM mytable OFFSET 0) ss
    WHERE id LIKE '%z%' AND score > 0.5;

Keep in mind that in the above formulation, expensivefunc will be
evaluated at rows that don't pass the LIKE test.  So you probably want
to push down as much as you can into the sub-select's WHERE clause.
The planner will not help you with that if you put in the OFFSET 0
optimization-fence.  It's a good idea to use EXPLAIN (or even better
EXPLAIN VERBOSE, if you're using >= 8.4) to confirm that you're getting
the plan you want.

            regards, tom lane