postgresql How to share cte among different tables in plain sql?

Question

Say select id from some_expensive_query is the cte I want to share. Currently I write two sql in a transaction:

with t as (select id from some_expensive_query) select * from  t1 join t on t.id =t1.id;
with t as (select id from some_expensive_query) select * from  t2 join t on t.id =t2.id;

As you can see, the cte is executed twice but I want something like:

t = select id from some_expensive_query;
select * from  t1 join t on t.id =t1.id;
select * from  t2 join t on t.id =t2.id;

for portability, I don't want to use pgsql or functions, anyway to solve this?

Answer 1

Why don't you use union all ?

with t as (select id from some_expensive_query) 
select * from  t1 join t on t.id =t1.id
union all
select * from  t2 join t on t.id =t2.id;