In response to Bob Price <rjp_email@xxxxxxxxx>: > 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 the community. > > 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. > > As a simple example, consider the following query on a table with 'id' and 'value' columns, and an expensive computation represented 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 the other WHERE constraints are not satisfied. Two ways that I can think of: 1) If expensivefunc() doesn't have any side-effects, you can create it as IMMUTABLE, which tells PostgreSQL that it can cache the result for optimization purposes. IMMUTABLE is not the default. 2) Create a new column in the table that stores the value of expensivefunc(value) and add a trigger to the table to ensure that column is updated any time value is changed. This will slow down inserts and updates a bit, but it means you can select/compare the generated column directly with no calculation. Which one of these is more practical for you depends on a number of factors about the table, the data, and the function. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
