how to avoid repeating expensive computation in select - Mailing list pgsql-general

I have been searching through the docs and mailing list and haven't found a way to do this, so I thought I would ask
thecommunity. 

I would like to know if there is a way in PostgreSQL to avoid repeating an expensive computation in a SELECT where the
resultis needed both as a returned value and as an expression in the WHERE clause. 

As a simple example, consider the following query on a table with 'id' and 'value' columns, and an expensive
computationrepresented as a function: 

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

It would be great if I could find a way to only compute expensivefunc(value) at most once per row, and not at all if
theother WHERE constraints are not satisfied. 

For this simple case I know that I could rewrite the SELECT as something like the following:

WITH other_where AS (
    SELECT id, value FROM mytable WHERE id LIKE '%z%'
  ), calc_scores AS (
    SELECT id, expensivefunc(value) AS score FROM other_where
  )
SELECT id, score from calc_scores WHERE score > 0.5;

This works in this simple case, but my guess is that it probably adds a lot of overhead (is this true?), and I also
haveto deal with much more complicated scenarios with multiple expensive calculations that may not fit into this kind
ofrewrite. 

Does anyone know of a simpler way to accomplish this?

For example, it would be great if there were a function that could reference the Nth select list item so it is only
computedonce, like: 

  SELECT id, expensivefunc(value) AS score FROM mytable
     WHERE id LIKE '%z%' AND sel_list_item(2) > 0.5;

or if there were temporary variables in the WHERE expressions like:

  SELECT id, tmp1 AS score FROM mytable
     WHERE id LIKE '%z%' AND (tmp1 = expensivefunc(value)) > 0.5;

Any ideas anyone!

Thanks in advance!
Bob