Is using a pl/pgsql function a viable option. Within or without the use of a function you can create a temporary table to hold the needed intermediate results. You can even use a permanent working table and write functions to perform the needed queries against it. Especially for expensive calculation you want to consider whether it is safe/reasonable to pre-calculate and store values instead of running the calculation during each query. If you need procedural language capabilities (variables, multiple uses of the same data) trying to work out a solution in pure transactional SQL can be difficult or outright impossible; you really need to use the procedural facilities built into the server OR your application environment. In other words put down the hammer and go find yourself a chainsaw :) David J -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Bob Price Sent: Thursday, February 03, 2011 12:18 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: how to avoid repeating expensive computation in select 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. 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 have to deal with much more complicated scenarios with multiple expensive calculations that may not fit into this kind of rewrite. 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 computed once, 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
