1.数组类型的字段
定义一个包含字段的表:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
1)用字符串形式输入数组数据:用{}包含,用,分隔
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
3)用[]访问数组字段中的元素:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
4)使用[lower-bound:upper-bound]方式获取数组的切片,或者子数组:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
array_dims
函数获取数组的维度![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2.索引
2.1基于表达式的索引
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
建立了一个lower(col1)表达式的索引。
2.2 局部索引
局部索引是建立在一部分记录(满足一个过滤条件)上的索引。假设有以下的表
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
3. 规则系统
EnterpriseDB的规则系统(准确的说,查询重写规则系统),和Trigger的概念有点类似,但是实现方式完全不同。规则系统通过修改查询来引入规则,然后把修改后的查询传递给后续的执行计划系统。
规则系统介于语法分析器和执行计划之间,它接受分析器的输出(一个查询树),以及用户定义的查询重写规则(也是一个查询树),然后创建一个或者多个语法树作为输出传递给执行计划系统。
那么,什么是查询树?查询数是SQL的语法树(从这一点来说,是SQL树而不是查询树,因为查询树容易误解为SELECT语句的语法树。)EnterpriseDB提供了输出语法树的函数:debug_print_parse, debug_print_rewritten, debug_print_plan。
语法树中的重要部分:
1)命令类型,标识SQL语句的类型,SELECT, UPDATE或DELETE等
2)范围表。既SQL语句中用到的关系(表、视图),如果为SELECT语句,就是FROM和WHERE之间的表或视图。
3)结果表。标识SQL执行后产生的结果表。SELECT INTO语句会产生结果表。对于UPDATE,INSERT,DELETE,结果表为受影响的表。
4)目标列表:一系列表达式,定义了结果表的字段。
a)对于SELECT语句,是SELECT和FROM关键字之间的内容。
b)DELETE语句没有目标类表(但实际上,规则系统会在目标类表中增加一个CTID)。
c)对于INSERT语句,目标列表描述插入到目标表中的内容,包括VALUES后的内容,或INSERT ... SELECT... 方式中的SELECT语句中的内容。重写过程的第一步是在目标列表中增加在SQL中没有出现,但包含默认值的字段,其他字段将有执行器添加,其值为Null。
d)对于UPDATE命令,目标列表为将替换老记录的新纪录表达式,既SET column = expression部分。执行器将添加其他字段(值为老记录值)。规则系统将和DELETE语句类似的添加CTID。
目标列表中的每个元素都是表达式,由常量、指向范围表的一个字段名,参数,或一个函数调用子树。
5)过滤条件(原文为Qualification)
过滤条件和目标列表中的一个元素类似,是一个表达式,其结果类型为BOOL类型。对应于SQL中的WHERE子句的内容。
6)连接树
当多表连接使用了ON、USING子句时,用连接树来表现不同表之间的连接关系。实际上,WHERE中的某些部分可能也和多表连接有关,因此WHERE中的某些内容也会出现在连接树中。
7)其它部分,如ORDER BY
3.1 视图和规则系统
EnterpriseDB中的视图使用规则系统实现。实际上,视图和规则系统没有本质的区别。比如![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
3.1.1 SELECT规则如何起作用
规则ON SELECT作用到查询上,规则的命令可以不仅仅是SELECT,也可以是INSERT,UPDATE,DELETE。和其他命令不同,它修改了原有的语法树,而不是创建一个新的语法树。
目前的版本ON SELECT规则的动作(Action)只能有一个,并只能是INSTEAD SELECT。
例子:
先创建一个min函数,返回两个数中的小值:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
规则中涉及的表--鞋、鞋带和换算单位:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
涉及到的几个视图
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE VIEW命令shoelace创建了一个关系shoelace,以及pg_rewrite中的一个实体来说明,当引用到shoelace视图时,执行一个重写规则,这个规则没有过滤条件,没有SELECT规则,是一个替换(INSTEAD)规则。
需要注意规则的过滤条件不同于查询的过滤条件。规则的动作(action)包含查询过滤条件。规则的动作的查询树是查询定义命令的拷贝。
下面是对涉及到的表和视图的操作:
INSERT INTO unit VALUES ('cm', 1.0); INSERT INTO unit VALUES ('m', 100.0); INSERT INTO unit VALUES ('inch', 2.54); INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm'); INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch'); INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm'); INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch'); INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm'); INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm'); INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch'); INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch'); INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm'); INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm'); INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm'); INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch'); SELECT * FROM shoelace; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm -----------+----------+----------+--------+---------+----------- sl1 | 5 | black | 80 | cm | 80 sl2 | 6 | black | 100 | cm | 100 sl7 | 7 | brown | 60 | cm | 60 sl3 | 0 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 sl8 | 1 | brown | 40 | inch | 101.6 sl5 | 4 | brown | 1 | m | 100 sl6 | 0 | brown | 0.9 | m | 90 (8 rows)
其中的SELECT命令被解析器解析为:
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;
所产生的语法树输出到规则系统后,对shoelace视图进行展开:
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace *OLD*, shoelace *NEW*,
shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
这和下列查询的结果类似
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM (SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) shoelace;
然而,两者之间还是有些区别:基于视图的查询包含两个特殊的实体shoelace *OLD* 和shoelace *NEW*。这两个实体并不直接参与查询,因为它们没有出现在连接树或者目标列表节点中。规则系统使用这两个实体来引用在系统表中的权限信息,来确保SQL执行用户有合适的权限。
第二个例子,查询和鞋带配对、库存量大于2的鞋:
SELECT * FROM shoe_ready WHERE total_avail >= 2; shoename | sh_avail | sl_name | sl_avail | total_avail ----------+----------+---------+----------+------------- sh1 | 2 | sl1 | 5 | 2 sh3 | 4 | sl7 | 7 | 4 (2 rows)
解析器生成的结果类似于下面的查询:
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM shoe_ready shoe_ready
WHERE shoe_ready.total_avail >= 2;
sheo_ready视图规则作用于查询语法树后生成:
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail >= 2;
再次展开shoe和shoelace后生成一个三层的语法树:
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name) rsh,
(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail > 2;
在执行计划中,这个树会被优化为一个两层的树:最底层的SELECT将被合并到中间一层的SELECT,但不会和第一层合并,因为包含了一个min聚合函数,提升这一层将会改变顶层SELECT的行为。这个优化动作实在执行计划中进行的,与规则系统无关。
3.1.2 非SELECT语句的规则
查询树有两个特性在上述例子中还没有涉及。它们是命令类型和结果表。实际上,视图规则不需要这些信息。SELECT命令和其他命令的语法树有几点不同。很明显,命令类型和SELECT命令不同,同时,结果表标明命令运行结果实体在系统实体表中的位置。查询树中的其余部分相同。比如,下列两个SQL:UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;
它们的语法树基本相同。
1)范围表为t1和t2
2)目标列表包含t2的b字段
3)过滤条件表达式比较t1,t2的a字段
4)连接树用a字段相同方式连接t1, t2
4 关于INSERT, UPDATE, DELETE的规则
定义在INSERT、UPDATE和DELETE的规则和视图规则差异很大。首先,它们的CREATE RULE命令支持更多的语法:
- 可以是INSTEAD或ALSO(默认) ;
- 两个伪表NEW、OLD可以发生作用;
- 它们可以包括规则过滤条件。
其次,它们创建一个或多个新的语法树来取代原有语法树,而不是修改原有语法树。
4.1 更新规则如何起作用
创建规则的语法:![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
当规则系统判断将被执行的语法树的表和事件和规则匹配时,规则将被启用,所产生的语法树被传递到执行器进行执行。
规则的过滤条件用来判断什么情况下规则可以运行,什么时候不需要。规则的过滤条件只引用两个伪表NEW和OLD。同时,原语法树的过滤条件会被添加到规则的过滤条件中。
ALSO关键字意味着原语法树会被增加到规则语法树列表中。
对于ON INSERT规则,ALSO关键字下,原语法树在规则的语法树之前执行。这时规则可以看到新增加的数据。对于ON UPDATE和ON DELETE规则,原语法树在规则的语法树之后执行,这保证规则能够看到被修改的记录。
例子
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
我们建立一个规则,来跟踪对shoelace_data的sl_avail字段的修改,并记录到shoelace_log中。在进行了以下更新后:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
1.解析器创建的查询树:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
5.加入原SQL的过滤条件
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)