# Making intermediate CTE results available for output/query without reprocessing earlier CTEs

Say I have the following query:

``````WITH
d AS
( SELECT x, y FROM test WHERE x > 0 AND y > 0
)
, sc_x AS
( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE x >= 5
)
, sc_y AS
( SELECT AVG(x) av_x, AVG(y) av_y FROM d WHERE y >= 5
)
SELECT
SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
sc_x
, sc_y
``````

Let's pretend those calculations are super complex and make sense to have in separate CTEs (I know these aren't). I need to query each of the CTE results and the final results for output for a program. I am thinking the best way to do this is to remove the CTEs and do SELECT ?? INTO like this for each CTE:

``````DROP TABLE d IF EXISTS;
SELECT x, y into d FROM test WHERE x > 0 AND y > 0;
DROP TABLE sc_x IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_x FROM d WHERE x >= 5;
DROP TABLE sc_y IF EXISTS;
SELECT AVG(x) av_x, AVG(y) av_y into sc_y FROM d WHERE y >= 5;
SELECT SUM(sc_x.av_x)+SUM(sc_x.av_y)+SUM(sc_y.av_x)+SUM(sc_y.av_y)
FROM
sc_x
, sc_y;
``````

Then query these tables separately. Is that the best strategy? I don't see another way to make the CTE results available without recalculating them. Speed is an issue, so I don't want to do all of the processing again that occurs before the CTE I want for output. Hopefully that makes sense.

PostgreSQL CTE page: https://www.postgresql.org/docs/current/static/queries-with.html

I have Postgres 9.6.

• What do you mean by recalculating? That the result set of a CTE is once built, and if you query it twice, it's built twice? This latter is not the case. – dezso Aug 30 '17 at 13:39
• Thanks for helping. The context is that I always need the final results/select. If I also want CTE sc_x for output and I don't do the Select Into stuff, I have to run the query for CTE d and CTE sc_x over again. In the real world these take a long time. So creating the intermediate tables using Select Into makes all of the results available...but doing it with the select intos I have to create a bunch of tables, which is a bit messy in my opinion. – mountainclimber Aug 30 '17 at 13:46
• Well, if performance is really a matter, a bit of mess in the code cannot hurt :) Otherwise, you can create those tables as temporary ones, adjust `work_mem` and `temp_buffers`, wrap the whole thing in a function and hopefully done. Otherwise, seeing an actual execution plan of your query could help coming up with better ideas. – dezso Aug 30 '17 at 13:53
• Unrelated, but: it is recommended to use `create table new_table as select` instead of the non-standard `select ... into new_table ...` – a_horse_with_no_name Aug 30 '17 at 19:51
• @a_horse_with_no_name - how would the syntax work for that? I got `with my_cte as ([query here]) select * into new_table from ...` to work, but not `with my_cte as ([query here]) create table as select ...` – mountainclimber Aug 31 '17 at 16:02