postgresql如何在普通sql中的不同表之间共享cte

Question

假设select id from some_expensive_query是我想要共享的cte。目前我在一个事务中写两个sql：

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;

正如你所看到的，cte被执行了两次，但我想要这样的东西：

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;

为了便于移植，我不想使用pgsql或函数来解决这个问题？

Answer 1

为什么不使用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;