PG中的视图

0    474    6

👉 本文共约6012个字,系统预计阅读时间或需23分钟。

简介

View(视图)是一张假表,只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句。

View(视图)实际上是一个以预定义的 PostgreSQL 查询形式存在的表的组合。

View(视图)可以包含一个表的所有行或从一个或多个表选定行。

View(视图)可以从一个或多个表创建,这取决于要创建视图的 PostgreSQL 查询。

View(视图)是一种虚拟表,允许用户实现以下几点:

  • 用户或用户组认为更自然或直观查找结构数据的方式。
  • 限制数据访问,用户只能看到有限的数据,而不是完整的表。
  • 汇总各种表中的数据,用于生成报告。

PostgreSQL 视图是只读的,因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器,当尝试 DELETE、INSERT 或 UPDATE 视图时触发,需要做的动作在触发器内容中定义。

CREATE VIEW(创建视图)

在 PostgreSQL 用 CREATE VIEW 语句创建视图,视图创建可以从一张表,多张表或者其他视图。

CREATE VIEW 基础语法如下:

您可以在 SELECT 语句中包含多个表,这与在正常的 SQL SELECT 查询中的方式非常相似。如果使用了可选的 TEMP 或 TEMPORARY 关键字,则将在临时数据库中创建视图。

实例

创建 COMPANY 表,数据内容如下:

现在,下面是一个从 COMPANY 表创建视图的实例。视图只从 COMPANY 表中选取几列:

现在,可以查询 COMPANY_VIEW,与查询实际表的方式类似。下面是实例:

得到结果如下:

DROP VIEW (删除视图)

要删除视图,只需使用带有 view_name 的 DROP VIEW 语句。DROP VIEW 的基本语法如下:

下面的命令将删除我们在前面创建的 COMPANY_VIEW 视图:

PG中的临时视图

与oracle不同,pg可以创建临时视图

创建完后,在别的会话中是无法查看该视图的,这个会话退出后,这个视图的定义也被清除掉了

可更新视图

从PG 9.3开始,创建的简单视图,默认是可以更新的。如果是老版本,那么可以通过定义一个规则来更新视图。

需要创建规则来使视图可更新

同理插入,删除都需要创建规则

删除

视图和规则系统

http://postgres.cn/docs/13/rules-views.html

PostgreSQL中的视图是通过规则系统来实现的。事实上,下面的命令

与下面两个命令相比没有不同:

因为这就是CREATE VIEW命令在内部所作的。 这样做有一些副作用。其中之一就是在PostgreSQL系统目录中的视图信息与表的信息完全一样。所以对于解析器来说,表和视图之间完全没有区别。它们是同样的事物:关系。

1. SELECT规则如何工作

规则ON SELECT被应用于所有查询作为最后一步,即使给出的是一条INSERTUPDATEDELETE命令。而且它们与其他命令类型上的规则有着不同的语义,它们会就地修改查询树而不是创建一个新的查询树。因此我们首先描述SELECT规则。

目前,一个ON SELECT规则中只能有一个动作, 而且它必须是一个无条件的INSTEADSELECT动作。 这个限制是为了令规则足够安全,以便普通用户也可以打开它们,并且它限制ON SELECT规则使之行为类似视图。

本章的例子是两个连接视图,它们做一些运算并且某些更多视图会轮流使用它们。最前面的两个视图之一后面将利用对INSERTUPDATEDELETE操作增加规则的方法被自定义,这样最终结果将是一个视图,它表现得像一个具有魔力的真正的表。这个例子不适合于作为简单易懂的例子,它可能会让本章更难懂。但是用一个覆盖所有关键点的例子来一步一步讨论要比举很多例子搞乱思维好。

在前两个规则系统描述中我们需要真实表是:

如你所见,它们表示鞋店的数据。

视图被创建为:

创建shoelace视图的CREATE VIEW命令(也是最简单的一个)将创建一个shoelace关系和一个pg_rewrite项, 这个pg_rewrite项说明有一个重写规则,只要一个查询的范围表中引用了关系shoelace,就必须应用它。该规则没有规则条件(稍后和非SELECT规则一起讨论,因为目前的SELECT规则不能有规则条件)并且它是INSTEAD规则。要注意规则条件与查询条件不一样。我们的规则的动作有一个查询条件。该规则的动作是一个查询树,这个查询是视图创建命令中的SELECT语句的一个拷贝。

注意

你在pg_rewrite项中看到的两个额外的用于NEWOLD的范围表项不是SELECT规则感兴趣的东西。

现在我们填充unitshoe_datashoelace_data,并且在视图上运行一个简单的查询:

这是你可以在我们的视图上做的最简单的SELECT,所以我们用这次机会来解释视图规则的基本要素。SELECT * FROM shoelace会被解析器解释并生成下面的查询树:

然后这将被交给规则系统。规则系统遍历范围表,检查有没有可用于任何关系的规则。在为shoelace(到目前为止的唯一一个)处理范围表时, 它会发现查询树里有_RETURN规则:

要扩展该视图,重写器简单地创建一个子查询范围表项,它包含规则的动作的查询树,然后用这个范围表记录取代原来引用视图的那个。作为结果的重写后的查询树几乎与你键入的那个一样:

不过有一个区别:子查询的范围表有两个额外的项shoelace oldshoelace new。这些项并不直接参与到查询中,因为它们没有被子查询的连接树或者目标列表引用。重写器用它们存储最初出现在引用视图的范围表项中表达的访问权限检查信息。以这种方式,执行器仍然会检查该用户是否有访问视图的正确权限,尽管在重写后的查询中没有对视图的直接使用。

这是被应用的第一个规则。规则系统将继续检查顶层查询里剩下的范围表项(本例中没有了),并且它将递归的检查增加的子查询中的范围表项,看看其中有没有引用视图的(不过这样不会扩展oldnew — 否则我们会得到无限递归!)。在这个例子中,没有用于shoelace_dataunit的重写规则,所以重写结束并且上面得到的就是给规划器的最终结果。

现在我们想写一个查询,它找出目前在店里哪些鞋子有匹配的(颜色和长度)鞋带并且完全匹配的鞋带双数大于等于二。

这词解析器的输出是查询树:

第一个被应用的规则将是用于shoe_ready的规则并且它会导致查询树:

相似地,用于shoeshoelace的规则被替换到子查询的范围表中,得到一个三层的最终查询树:

这看起来是低效的,但是计划器会通过“pulling up”子查询将其折叠成一个单层查询树,然后它会计划联接, 就像我们手动写出来一样。 因此,折叠查询树是重写系统本身不必关心的一种优化。

2. 非SELECT语句中的视图规则

有两个查询树的细节在上面的视图规则的描述中没有涉及。它们是命令类型和结果关系。实际上,视图规则不需要命令类型,但是结果关系可能会影响查询重写器工作的方式,因为如果结果关系是一个视图,我们需要采取特殊的措施。

一个SELECT的查询树和其它命令的查询树之间很少的几处不同。显然,它们有不同的命令类型并且对于SELECT之外的命令,结果关系指向结果将进入的范围表项。其它所有东西都完全相同。所以如果有两个表t1t2分别有列ab,下面两个语句的查询树:

几乎是一样的。特别是:

  • 范围表包含表t1t2的项。
  • 目标列表包含一个变量,该变量指向表t2的范围表项的列b
  • 条件表达式比较两个范围表项的列a以寻找相等。
  • 连接树展示了t1t2之间的一次简单连接。

结果是,两个查询树生成相似的执行计划:它们都是两个表的连接。 对于UPDATE语句,规划器把t1缺失的列加到目标列并且最终查询树读起来是:

因此在连接上运行的执行器将产生完全相同的结果集:

但是在UPDATE中有个小问题:执行器计划中执行连接的部分不关心连接的结果的含义。它只是产生一个行的结果集。一个是SELECT命令而另一个是由执行器中的更高层处理的UPDATE命令,在那里执行器知道这是一个UPDATE,并且它知道这个结果应该进入表t1。但是这里的哪些行必须被新行替换呢?

要解决这个问题,在UPDATEDELETE语句的目标列表里面增加了另外一个项:当前元组 ID(CTID)。这是一个系统列,它包含行所在的文件块编号和在块中的位置。在已知表的情况下,CTID可以被用来检索要被更新的t1的原始行。在添加CTID到目标列之后,该查询实际看起来像:

现在,另一个PostgreSQL的细节进入到这个阶段了。表中的旧行还没有被覆盖,这就是为什么ROLLBACK很快的原因。在一个UPDATE中,新的结果行被插入到表中(在剥除CTID之后),并且把CTID指向的旧行的行头部中的cmaxxmax项设置为当前命令计数器和当前事务 ID 。这样旧的行就被隐藏起来,并且在事务提交之后 vacuum 清理器就可以最终移除死亡的行。

知道了所有这些,我们就可以用完全相同的方式简单地把视图规则应用到任意命令中。没有任何区别。

3. PostgreSQL中视图的能力

上文演示了规则系统如何把视图定义整合到原始的查询树中。在第二个例子中,一个来自于一个视图的简单SELECT创建了一个四表连接(unit以不同的名字被用了两次)的最终查询树。

用规则系统实现视图的好处是,规划器拥有关于哪些表必须被扫描、这些表之间的联系、来自于视图的限制性条件、一个单一查询树中原始查询的条件等所有信息。当原始查询已经是一个视图上的连接时仍然是这样。规划器必须决定执行查询的最优路径,而且规划器拥有越多信息,该决定就越好。并且PostgreSQL中实现的规则系统保证这些信息是此时能获得的有关该查询的所有信息。

4. 更新一个视图

如果视图是INSERTUPDATEDELETE的目标关系会怎样?使用上文所述的替换将给出一个查询树,其中的结果关系指向一个子查询范围表项,这样无法工作。不过,PostgreSQL中有几种方法来支持更新视图。

如果子查询从一个单一基本关系选择并且该关系足够简单,重写器会自动地把该子查询替换成底层的基本关系,这样INSERTUPDATEDELETE会被以适当的方式应用到该基本关系。其中“足够简单”的视图被称为自动可更新。有关这种可以被自动更新的视图类别的详细信息,请见CREATE VIEW

或者,该操作可以被定义在视图上的一个用户提供的INSTEAD OF触发器处理。在这种情况下重写工作有一点点不同。对于INSERT,重写器对视图什么也不做,让它作为查询的结果关系。对于UPDATEDELETE,仍有必要扩展该视图查询来产生命令将尝试更新或删除的“旧”行。因此该视图被按照通常的方式扩展,但是另一个未被扩展的范围表项会被增加到查询来表示该视图会尽其所能作为结果关系。

现在出现的问题是如何标识在视图中要被更新的行。回忆一下,当结果关系是一个表时,一个特殊的CTID项会被加入到目标列表来标识要被更新的行的物理位置。如果结果关系是一个视图这就行不通,因为一个视图根本就没有CTID,它的行没有实际的物理位置。对于一个UPDATEDELETE操作,一个特殊的wholerow项会被增加到目标列表中,它会扩展来包括来自该视图的所有列。执行器使用这个值来提供“旧”行给INSTEAD OF触发器。现在就轮到触发器来基于新旧行值来找出要更新什么了。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

另外一种可能性是让用户定义INSTEAD规则,这种规则指定对视图上的INSERT\UPDATEDELETE命令的替代动作。这些规则将重写该命令,通常是重写成一个更新一个或多个表(而不是视图)的命令。这是第 40.4 节的主题。

注意规则会首先被计算,然后在原始查询被规划和执行之前重写它。因此,如果一个视图上同时有INSTEAD OF触发器和INSERTUPDATEDELETE规则,那么首先会计算规则,然后根据其结果决定是否执行触发器,触发器可能完全都不会被使用。

Automatic rewriting of an 在一个简单视图上的INSERTUPDATEDELETE查询的自动重写总是在最后尝试。因此,如果一个视图有规则或触发器,它们将重载自动可更新视图的默认行为。

如果对该视图没有INSTEAD规则或INSTEAD OF触发器,并且重写器不能自动地把该查询重写成一个底层基本关系上的更新,将会抛出一个错误,因为执行器不能更新一个这样的视图。

    头像

    小麦苗

    学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    12 − 10 =

     

    嘿,我是小麦,需要帮助随时找我哦
    • 18509239930
    • 个人微信

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部