PostgreSQL7.0手册-程序员手册 -40. 扩展的 SQL: 操作符
2019-09-08 23:33:46
供稿：网友
第四十章. 扩展的 SQL: 操作符
Postgres 支持左目，右目和双目操作符．操作符可以重载，或以不同数目或类型的参数重新使用．如果有语义含混而系统无法决定使用哪个正确的操作符，它将返回一个错误。你可能必须转换左和/或右操作数的类型来帮助系统明白你想用的是哪个操作符．
每个操作符都是对真正干活的对应函数的"语义修饰"；所以你在创建操作符之前必须先创建对应的函数。不过，一个操作符也并不仅仅是语义修饰，因为它还带着可以帮助查询规划器优化使用该操作符的查询的附加信息。本章的大部分将用于解释那些附加信息。
下面是一个创建用于两个复数相加的操作符的例子。我们假设已经创建了复数类型的定义。首先我们需要做（相加）工作的函数；然后我们就可以定义操作符：
CREATE FUNCTION complex_add(complex, complex)
RETURNS complex
AS '$PWD/obj/complex.so'
LANGUAGE 'c';
CREATE OPERATOR + (
leftarg = complex,
rightarg = complex,
procedure = complex_add,
commutator = +
);
现在我们可以：
SELECT (a + b) AS c FROM test_complex;
+----------------+
|c |
+----------------+
|(5.2,6.05) |
+----------------+
|(133.42,144.95) |
+----------------+
我们在这里已经演示了如何创建两目操作符。要创建单目操作符，只需要省略左操作数（对左目操作符）或者右操作数（对右操作符）即可。只有过程（procedure）子句和参数（argument）子句是 CREATE OPERATOR 里需要的条目。例子里演示的 COMMUTATOR 子句是一个给查询优化器的可选的暗示。关于 COMMUTATOR 和其它优化器暗示的详细信息在下面给出。
操作符优化信息
作者：由 Tom Lane 书写。
Postgres 的操作符定义可以包括几个可选的子句，这些子句告诉系统一些关于该操作符的特性的有用信息。在可能的情况下，我们都应该提供这些子句，因为它们可能为使用这个操作符的查询带来可观的速度提升。不过要注意如果你声明了这些子句，你必须确保它们是正确的！对优化子句的错误使用将导致后端的崩溃，精细的输出错误或者其他糟糕事情。如果你对这些事情不确定的话，你可以总是忽略优化子句；唯一的后果是查询可能比需要的运行的慢一些。
附加的优化子句可能在今后的 Postgres 版本里出现。这里描述的都是版本 6.5 可以理解的。
COMMUTATOR（换向器）
如果提供了 COMMUTATOR 子句，则命名一个操作符是被定义的操作符的换向器。如果有两个操作符 A，B，对于任何可能的输入 x，y 都有 (x A y) 等于 (y B x)，那么我们就说 A 是 B 的换向器，同样 B 也是 A 的换向器。例如，操作符 '<' 和 '>' 对于所使用的一定的数据类型通常都是对方的换向器，而操作符 '+' 通常是它自身的换向器。但是操作符 '-' 通常没有换向器。
一个被换向的操作符的左操作符与它的换向器的右操作符相同，反之亦然。所以Postgres 所需要的只是一个换向器操作符的名称用以查找该换向器，那也是 COMMUTATOR 子句里所需要的唯一的东西。
当你定义一个自换向的操作符时，你定义它就是了。当你定义一对换向器操作符时，事情就有一点棘手：怎样定义一个操作符的换向器指向另一个你还没有定义的操作符呢？我们对这个问题有两个解决方法：
个方法是省略你定义的第一个操作符的 COMMUTATOR 子句，然后在第二个操作符的定义里提供一个（ COMMUTATOR 子句）。因为 Postgres 知道换向操作符是成对出现的，所以当它看到第二个定义时它会自动返回并填充第一个定义里空缺的 COMMUTATOR 子句。
另一个更直接的方法是在两个定义里面都包含 COMMUTATOR 子句。当 Postgres 处理第一个定义并意识到 COMMUTAOTR 指向一个不存在的操作符，系统会在系统的 pg_operator 表里面为该操作符记录一个虚拟的记录。这个虚拟的记录只有操作符名，左和右参数类型以及结果类型是有效的，因为这些是到目前为止Postgres 可以推导出来的东西。第一个操作符表记录将和这个虚拟记录联接。稍后，当你定义第二个操作符时，系统将用来自第二个操作符的信息更新该虚拟记录。如果你试图在虚拟操作符被填充之前使用它，你将只能收到一条错误信息。（注意：这个过程在Postgres 6.5以前的版本不能可靠的工作，但是现在这种方法是我们推荐的方法。）
NEGATOR（否定符）
如果提供了 NEGATOR 子句，则命名一个操作符是被定义的操作符的否定符。如果有两个都返回布尔变量的操作符 A 和 B，对任何可能的输入 x 和 y，都有 (x A y) 等于 NOT (x B y)，那么我们说 A 是 B 的否定符。当然 B 也是 A 的否定符。例如，'<' 和 '>=' 对大多数数据类型是一对否定符。一个操作符不可能是它自身的有效操作符。
不象 COMMUTATOR，一对单目操作符可以互为有效的否定符；那就意味着对于所有的 x，(A x) 等于 NOT (B x)，或者类似的右目操作符的这种情况。
一个操作符的否定符必须有于该操作符本身一样的左和/或右参数，所以就象 COMMUTATOR 一样，只有操作符名需要在 NEGATOR 子句里面给出。
提供 NEGATOR 对查询优化器是非常有帮助的，因为这样就允许象 NOT (x = y) 这样的表达式简化成 x <> y。这样的情况比你想象的要频繁的多，因为 NOT 可能因为其他的重排列而被引入。
否定符对可以用上面换向符对中解释的相同的方法来定义。
RESTRICT（限制）
如果提供了 RESTRICT 子句，则为操作符命名一个选择性限制计算函数（注意这里是一个函数名，而不是一个操作符名）。RESTRICT 子句只是对返回布尔变量的双目操作符有意义。选择性限制计算符的概念是猜测一个表中所有行的哪一部分对于目前的操作符和特定的常量将满足一个象下面这样形式的 WHERE 条件子句
field OP constant
它可以给出这种类型的 WHERE 子句可以删除多少行的一个概念，这将帮助优化器进行优化。（你可能会说，如果该常量（constant）在左边怎么办？哦，那是 COMMUTATOR 干的事...）
书写新的选择性限制计算函数远远超出了本章的范围，不过很幸运的是，通常你对自己的操作符只需要使用系统标准的计算器之一就行了。下面是一些标准限制计算器：
eqsel for =
neqsel for <>
scalarltsel for < or <=
scalargtsel for > or >=
这些都是分类，看起来有点奇怪，不过如果你仔细想想，就会觉得有道理。'=' 大多将只接受表中的一小部分行；'<>' 大多将拒绝一小部分行。'<' 将接受的行取决于给出的常量落在表的该列数据值的哪一个范围里（该值碰巧是 VACUUM ANALYZE 收集并且提供给选择性计算器的信息）。'<=' 在同样的常量时会接受比 '<' 略微大一些的行，不过它们也非常接近，几乎不值得区别开来，尤其是无论如何我们也比做盲猜好得多。类似的情况也适用于 '>' 和 '>='。
你可能常习惯于把 eqsel 或者 neqsel 用于那些非常高或者非常低选择性的操作符，即使它们并非真正相等或者不相等。例如，规则表达式匹配操作符（~，~*，等）常使用 eqsel，是基于这样的假设：它们只会匹配整个表中的一小部分记录。
你可以把 scalarltsel 和 scalargtsel 用于比较那些为进行范围比较被转化为数字尺度后有明显意义的数据类型。如果可能，把该数据类型增加到可以被文件 src/backend/utils/adt/selfuncs.c 里的过程 convert_to_scalar() 理解的部分。（最终，这个过程将被放到由 pg_type 表里的一个列标识的每种类型一个的函数代替，不过目前还没有这么做。）如果你没有做这些，系统仍然能工作，不过优化器的估计不会象想象的那么好。
在 src/backend/utils/adt/geo_selfuncs.c 里还有为几何操作符设计的额外的选择性函数：areasel，positionsel，和 contsel。在我写这些的时候，它们都只是存根，但是你还是可以使用（或者更好的是，改良它们）它们。
JOIN（联合）
如果提供了 JOIN 子句，则为操作符命名一个联合选择性函数（注意这里是函数名，不是操作符名）。JOIN 子句只是对返回布尔量的双目操作符有意义。一个联合选择性计算器后面的概念是猜测一对表上的那一部分行对目前的操作符将满足下面形式的 WHERE 子句的条件
table1.field1 OP table2.field2
和 RESTRICT 子句一样，这些很有可能帮助优化器用最少的处理勾画出要采取可能的联合顺序中的哪一个。
和前面一样，本节不会试图解释如何书写一个联合选择性计算器函数，但是会建议你在有一个可用的情况下，使用一个标准的计算器：
eqjoinsel for =
neqjoinsel for <>
scalarltjoinsel for < or <=
scalargtjoinsel for > or >=
areajoinsel for 2D area-based comparisons
positionjoinsel for 2D position-based comparisons
contjoinsel for 2D containment-based comparisons
HASHES
如果出现了 HASHES 子句，则告诉系统对于一个基于此操作符的联合可以使用哈希（散列）联合。HASHES 只对返回布尔量的双目操作符有意义，并且实际上该操作符最好是对某种数据类型的相等操作符。
哈希（散列）联合的假设是：对于一对哈希（散列）到同样的哈希（散列）代码的左和右操作数值，该联合操作符只能返回 TRUE。如果两个值被放到不同的哈希桶里，联合将根本不比较它们，隐含地意味着联合操作符的结果一定是 FALSE。所以对于不代表相等的操作符，声明 HASHES 是没有意义的。
实际上，逻辑相等还不够好；该操作符最好是代表完全的按位相等，因为哈希函数将对该值的内存表现形式进行计算而不管这些位的含义是什么。例如，时间间隔的相等不是按位相等；时间间隔相等操作符认为如果两个时间间隔具有相同持续时间时就是相等的，而不管它们的两个端点是否相等。这就意味着对于一个用 "=" 在时间间隔域之间的联合，如果用哈希联合实现将会和用别的联合实现生成不同的结果，因为可以匹配的大部分数据对将被哈希成不同的值因而不会被哈希联合进行比较。但是如果优化器选择使用不同的联合方法，所有等号操作符说相等的数据对都会被找出来。我们不想出现那种不一致性，所以我们没有标记时间间隔等号为可哈希的。
同时还有一些硬件相关的因素会导致一个哈希联合的计算错误。例如，如果你的数据类型是一个结构，结构里可能有不引人注意的填充位，这时把这个等号操作符标记为 HASHES 也是不安全的。（除非你书写你的其他操作符以确保这些未用的位总是零。）另一个例子是 FLOAT 数据类型对哈希联合也是不安全的。在符合 IEEE 浮点标准的机器上，负零和正零是不同的值（不同的位模式），但是它们被定义为比较相等。所以，如果浮点等号被标记为 HASHES，一个负零和一个正零可能不被哈希联合匹配，但是用其他联合处理，它们应该是匹配的。
底线是：你可能只能把 HASHES 用于用（或可能用） memcmp（）实现的等号操作符。
SORT1 和 SORT2
如果出现了 SORT 子句，则告诉系统对基于目前的操作符可以使用融合联合方法。如果两者（左右数据类型）都是则都要声明。目前的操作符必须是对某一数据类型对的相等，并且 SORT1 和 SORT2 子句分别为左边和右边的数据类型命名了排序操作符（'<' 操作符）。
融合联合是以这样的概念为基础的：对左边和右边的表进行排序，然后并行地扫描它们。所以，两种数据类型都必须是能够完全排序的，并且联合操作符必须只对那些落在排序顺序中的"某个位置"的数值对成功。实际上这意味着联合操作符必须表现得象等于。但是和哈希联合不同，（哈希联合里左边和右边的数据类型最好是相同的（至少是按位相等）），可以对两种不同数据类型进行融合联合 -- 只要他们逻辑相等即可。例如， int2 对 int4 的相等操作符是可以用融合联合的。我们只需要可以把两种数据类型排列成逻辑可比的序列的排序操作符即可。
当声明融合排序操作符时，目前的操作符和两个引用的操作符必须返回布尔变量；SORT1 操作符的两个输入数据类型必须和目前操作符的左参数的类型相同，而 SORT2 操作符的两个输入数据类型必须和目前操作符的右参数的类型相同。（和 COMMUTATOR 已经 NEGATOR 一样，这意味着对于声明该操作符而言，操作符名称就足够了，并且如果你碰巧在另一个等于操作符之前定义一个等于操作符，系统能够自动填充虚拟操作符记录。）
实际上你只能为一个 '=' 操作符书写 SORT 子句，并且两个参考的操作符应该总是命名为 '<'。试图对命名为其他东西的操作符使用融合联合将导致让人绝望的冲突，我们一会儿就会看到原因。
还有一些对你标记为可融合连接的操作符的附加限制。这些限制目前没有被 CREATE OPERATOR 检查，但是如果下面之一是真的话，融合联合会在运行时失败：
可融合联合的相等操作符必须有一个换向器（如果两种数据类型相同则是它自身，如果不同则是一个相关的操作符）。
必须有和可融合联合操作符本身有相同左右输入数据类型的 '<' 和 '>' 排序操作符。这些操作符必须命名为 '<' 和 '>'；在这方面你没有任何选择，因为没有显式声明它们的规定。要注意如果左和右数据类型不同，这些操作符没有一个和 SORT 操作符中的任何一个相同。但是它们最好能对与 SORT 操作符兼容的数据值进行排序，否则融合联合将在工作中失效。
--------------------------------------------------------------------------------
