sql - 在 PostgreSQL 中选择而不创建新表

Question

我有很多这样的查询，它们的结果在最后合并，所以我可以计算两个不同结果集之间的连接数，然后使用联合查询生成完整的结果表。这里的问题是使用 select into 为每个结果创建一个新表而不是将其存储在内存中。使用 PostgreSQL 时，如何将每个结果存储在其自己的变量中，而不会在数据库中创建一大堆伪造的表，之后我将删除这些表？

select distinct cust_num into t1 
from all_visits_x_cust 
where date > '06/02/2015'
and date <= '07/02/2015' 
and cust_num > 9999;

select distinct cust_num into t1_2 
from cc_calls_x_cust 
where date = '06/02/2015' 
and cust_num > 9999);

select count(*) as post_call_visitors 
into v1 
from t1_2 join t1 
on t1.cust_num = t1_2.cust_num);

Answer 1

Postgres 中没有全局变量。您可以使用 with query将这些查询作为一个运行:

with t1 as (
    select distinct cust_num
    from all_visits_x_cust 
    where date > '06/02/2015'
    and date <= '07/02/2015' 
    and cust_num > 9999
    ),
t1_2 as (
    select distinct cust_num into t1_2 
    from cc_calls_x_cust 
    where date = '06/02/2015' 
    and cust_num > 9999
    )
select count(*) as post_call_visitors 
from t1_2 
join t1 
on t1.cust_num = t1_2.cust_num;

您可以在 PL/pgSQL function 中使用 SELECT ... INTO 变量或 DO statement .