CODEWITHSUNDEEP
sqlpostgresql
mstaniloiu
mstaniloiu

Reputation: 2795

PostgreSQL - use previously computed value from same query

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.

Upvotes: 3

Views: 3833

Answers (3)

Rob McDonell
Rob McDonell

Reputation: 1319

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.

Upvotes: 4

Wayne Conrad
Wayne Conrad

Reputation: 108259

A subselect might do it for you, e.g.:

select *, hard_to_compute * 2 as using_hard_to_compute \
from (select *, (i * i) as hard_to_compute from foo) bar;

 i | hard_to_compute | using_hard_to_compute 
---+-----------------+-----------------------
 1 |               1 |                     2
 2 |               4 |                     8
 3 |               9 |                    18

Of course, i * i isn't really hard to compute. I'm just pretending it is for this example.

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Your best bet might be to run the (SELECT ...) AS X portion of the query first, storing its results in a temporary table. Then use that temporary table in your main query.

Upvotes: 0

Related Questions