使用`WITH`创建的临时视图和使用`CREATE TEMPORARY VIEW`创建的临时视图有什么区别？

Question

在PostgreSQL中：

1. WITH子句创建了临时视图还是临时表？（如果我没记错的话，视图存储查询的代码，而表存储查询的结果）

2. CREATE TEMPORARY VIEW创建了一个仅在当前会话中可用的临时视图。那么WITH创建的临时视图和CREATE TEMPORARY VIEW创建的有何区别？

Database System Concepts似乎暗示WITH创建的是临时视图而不是临时表：

自SQL:1999版本以来，SQL标准支持一种有限的递归形式，使用with recursive子句，其中一个视图（或临时视图）以其自身为基础进行表达。递归查询可以用于简洁地表示传递闭包。请注意，with子句用于定义一个临时视图，其定义仅对定义它的查询可用。附加关键字recursive指定该视图是递归的。

- user3284469

---

那个引用出自哪里？ - Jasen

Answer 1

通用表达式 (CTE) 仅适用于单个查询。

临时视图 (类似于临时表) 可在当前会话的所有查询中使用。 它在会话结束时被删除。

- Gordon Linoff

---

谢谢。 (1) 通用表达式（CTE）是否像视图一样存储查询的代码，还是像表格一样存储查询的结果？(2) 什么是“会话”？ - user3284469

@Ben . . . 实际上，在Postgres中，公共表达式通常被评估并像临时表一样处理。 - Gordon Linoff

那么什么是“会话”？ - user3284469

从连接到Postgres直到断开连接的整个过程被称为一个会话。临时对象在会话中持续存在，例如：创建临时表...除非它们在会话结束之前被删除。 - Jasen

谢谢。在SQL标准和其他RDBMS中，CTE是临时表还是临时视图？我想知道“在Postgres中，CTE通常被评估并像临时表一样处理”是否只适用于PosrgreSQL？ - user3284469

@Ben... 两者都不是。它更类似于子查询，在整个查询过程中都可用。优化器可以决定是否将其材料化一次（更像表）或将代码插入到SQL语句中（更像视图）。 - Gordon Linoff

Answer 2

他们并不是临时视图。

在Postgres中，公共表达式（CTE）（WITH子句）被实现为类似于表的对象。而视图更像宏。

当其中一个列是具有副作用或返回不同值的函数时，这种效果最为明显。

select generate_series(1,3) as n into temp table a;  

一个简单的表格，包含1、2、3。

create temporary view v as select n,random() as r from a;
select * from v as x join v as y on x.n=y.n;   

使用视图时请注意，随机列不匹配。通过替换视图表达式可以获得相同的结果。

   select x.n,random(),y.n,random() 
   from a as x join a as y on x.n=y.n; 

或者

  select * from (select n,random() from a ) as x join
        (select n,random() from a )  as y on x.n=y.n;   

但是使用CTE：

with c as (select n,random() as r from a)
select * from c as x join c as y on x.n=y.n;   

使用CTE，请注意随机列的匹配。

另一种制作相同查询的方法是。

- Jasen

---

谢谢。在SQL标准和其他关系型数据库管理系统中，CTE是临时表还是临时视图？我想知道“在PostgreSQL中，CTE（WITH子句）被实现为类似于表的对象”是否只适用于PostgreSQL？ - user3284469

@Ben：SQL标准仅定义了SQL命令的语法和用户可见行为。它不对内部实现做任何假设。无论数据库是将数据“实体化”还是保留在内存中都不重要。这是一种优化策略。其他DBMS会在CTE变得太大或使用特殊语法元素时实体化CTE。最重要的是：VIEW是一个存储的对象（查询），始终可用（直到被删除）。CTE是单个查询的一部分，只在查询执行期间“存在”。 - user330315