PostgreSQL - use previously computed value from same query

Question

I have the following sql query:

SELECT (SELECT ...) AS X, (SELECT ...) AS Y from my_table

'X' is quite hard to compute, and it's used as an input for computing Y. However, if I try to reference X within the query that computes Y or even within the main query I get the following error message:

Error: column "X" does not exist

Is there any way to reference X once it is computed? I really don't want to compute it twice, as this seems very inefficient.

Answer 1

PostgreSQL is generally pretty clever in not having to compute the same thing twice. So a query like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute FROM whatever)*2 AS Y 
FROM my_table

should only need to run the two subqueries once. However if the query is like

SELECT (SELECT hard_to_compute FROM whatever) AS X,
       (SELECT hard_to_compute*2 FROM whatever) AS Y 
FROM my_table

then that might be harder for the optimiser to see what is the same in the two queries.

Another way you can handle this is to create a STABLE function to calculate X. As long as the input is the same, STABLE functions always return the same result within a single statement, so Postgres knows it only needs to run it once. See http://www.postgresql.org/docs/9.0/interactive/xfunc-volatility.html.