创建操作符

定义一个操作符

概要

  1. CREATE OPERATOR name ( 
  2.        PROCEDURE = funcname
  3.        [, LEFTARG = lefttype] [, RIGHTARG = righttype]
  4.        [, COMMUTATOR = com_op] [, NEGATOR = neg_op]
  5.        [, RESTRICT = res_proc] [, JOIN = join_proc]
  6.        [, HASHES] [, MERGES]
  7.        [, SORT1 = left_sort_op] [, SORT2 = right_sort_op]
  8.        [, LTCMP = less_than_op] [, GTCMP = greater_than_op] )

描述

CREATE OPERATOR 定义了一个新的操作符。定义操作符的用户变成它的拥有者。

操作符的名称是从以下列表中最长 NAMEDATALEN-1 （默认63）个字符的序列： + - * / < > = ~ ! @ # % ^ & | ` ?

用户选择的名称有一些限制

  • -- 和 /* 不能出现在操作符的任何位置，因为他们被认为是注释的开始。
  • 多字符操作符不能以 + 或 -结束， 除非该名字包含一个以下的字符： ~ ! @ # % ^ & | ` ?

例如， @- 是一个合法的操作符名字，但是 *- 却不是一个合法的操作符名字，该限制允许Greenplum数据库去解析复合SQL的命令，而不需要字符之间的空格。

操作符 != 在输出上映射为 <> ，所以这两个名字总是相等的。

至少 LEFTARG 和 RIGHTARG m有一个必须被定义。对于二进制操作符来说，两个都需要定义。对于右元操作符来说，只要 LEFTARG 需要定义，然而对于左元操作符来说，只要 RIGHTARG 被定义就行了。

该 funcname 程序必须使用 CREATE FUNCTION预定义，必须是 IMMUTABLE， 必须定义为能够接受正确的指定类型的参数个数（一个或者两个）

其他条款指出了可选的操作符优化子句，应当提供这些条款，以加快使用操作符的查询。 但是用户提供了这些条款，用户必须确保他们是正确的。不正确的使用优化子句可能导致服务进程的崩溃，错误输出或是其他意外结果。如果用户不确定，用户可以随时省略优化的句子。

参数

name

将要定义的操作符的（可选方案限定）名字。同一个模式中的两个操作符如果他们操作不同的数据类型，则他们可以有相同的名字。

funcname

实现该操作符的函数（必须是 IMMUTABLE 函数）。

lefttype

该操作符左操作数（如果有的话）的数据类型。对于左元操作符省略此选项。

righttype

该操作符右操作数（如果有的话）的数据类型。对于右元操作符省略此选项。

com_op

可选的 COMMUTATOR 子句命名了一个操作符，该操作符是正在定义的操作符的交换器。我们说，如果对于所有可能的输入值想x，y，（x A y）等于（y A x），则操作符A是操作符B的交换器。请注意，B也是A的交换器。例如 操作符 < 和 > 对于特殊的数据类型来说通常都是相互的转向器， 操作符+通常是与其自身可交换。但是操作符 - 通常不与任何操作符可交换，可交换操作符的左操作数与其交换器的右操作数类型相同，反之亦然。因此在 COMMUTATOR 子句中需要提供交换器的名称。

neg_op

可选的 NEGATOR 子句命名了一个操作符，该操作符是正在被定义的操作符的否定操作符。我们说，对于所有可能的输入x，y，如果（x A y）等于（y B x）而且两者都返回布尔类型的结果，则A操作符是B操作符的否定符，请注意，B操作符也是A操作符的否定符。例如， < 和 >= 对于大多数数据类型来说，是一个否定符号对。操作符否定符的左右操作数类型必须要与操作符的左右操作数的数据类型一样。所以只需在 NEGATOR 子句中指出操作符名称。

res_proc

可选的 RESTRICT 命名了操作符的限制选择性估计函数，请注意，这只是一个函数名称，不是操作符的名称。RESTRICT 子句仅对返回 boolean的二进制操作符才有意义。限制选择性估计器的想法是猜测表中的哪几行满足以下格式的WHERE子句条件：

  1. column OP constant

对于当前的操作符和特定的常数值。这有助于优化器，通过给出具有此表单的 WHERE 子句将消除多少行的想法。

用户通常可以为自己的操作符使用以下系统估计器函数之一:

eqsel用于=

neqsel用于<>

scalarltsel用于<或者<=

scalargtsel用于>或者>=

join_proc

可选的 JOIN 子句命令了操作符的连接选择性估计功能。请注意，这是一个函数名称，而不是操作符名称。JOIN 子句仅对返回boolean类型的二进制操作符才有意义。连接选择性估计器的想法是猜测一对表中的几个部分行将满足表单的 WHERE子句条件。

  1. table1.column1 OP table2.column2

对目前的操作符来说，这帮助优化器，通过让它明白可能的连接序列中哪一个可能占用最少的工作。

用户通常可以为许多自己的操作符使用以下系统标准连接选择性估计函数之一。

eqjoinsel用于=

neqjoinsel用于<>

scalarltjoinsel用于<或者<=

scalargtjoinsel用于>或者>=

areajoinsel用于基于2D区域的比较

positionjoinsel用于基于2D位置的比较

contjoinsel用于基于2D包含的比较

HASHES

可选的 HASHES 子句告诉系统，允许基于此操作符使用连接的哈希连接方法。HASHES 仅对返回boolean的二进制操作符才有意义。哈希连接操作符只能对左右值对都哈希到相同哈希码的情况才返回true。如果这两个值被放在了不同的哈希桶中，则该连接将永远不会比较他们。隐含的假设连接操作符的结果必须是假。因此，对于不代表相等性的操作符，指定 HASHES是没有意义的。

要标记为 HASHES，该连接操作符必须出现在哈希索引操作符类中。如果不存在这样的操作符类，试图在运行时的哈希连接中使用操作符将导致失败。系统需要操作符类来找到操作符输入的数据类型的特定哈希函数。用户还必须提供合适的哈希函数，然后才能创建操作符号类，在准备哈希函数时要小心，因为有依赖机器的方法，该方法将可能无法做正确的事情。

MERGES

MERGES 子句（如果存在）告诉系统可以使用基于此操作符的连接的合并方法。 MERGES 仅对于返回布尔值的二进制操作符才是有意义的，实际上，操作符必须表示某些数据类型或数据类型对的等式。

合并连接是基于按顺序排列的左右表格，然后并行扫描他们，因此，两种数据类型都能被完全排序，并且连接操作符必须只能对在排序顺序中位于相同位置的值对才能成功。实践中，这意味着连接符必须具有相等性。只要逻辑上兼容，就可以合并——连接两个不同的类型。例如，小整型对整数相等操作符是合并连接。我们只需排序操作符，这两个数据类型都将在成逻辑上相容的序列。

合并操作要求系统能够识别与合并连接相等操作符相关的四个操作符：小于左操作数数据类型的比较，小于右操作数数据类型的比较，在两种数据类型的小于比较和在两种数据类型的大于比较，也可以通过名称指定这些操作符，分别为SORT1， SORT2，LTCMP，和GTCMP。如果在指定了MERGES 时省略了这些名称，系统将填写默认名称。

left_sort_op

如果此操作符可以支持合并连接，则为对操作符左数据类型进行排序的小于操作符。如果没有指定，则为默认值<

right_sort_op

如果此操作符支持合并连接，则为对操作符右数据类型进行排序的小于操作符。如果没有指定，则为 <

less_than_op

如果此操作符支持合并连接，则为比较该操作符的输入数据类型的小于操作符。如果没有指定，则为<

greater_than_op

如果此操作符能支持合并连接，则为比较该操作符的输入数据类型的大于操作符。如果没有指定，则为默认值>。

要在可选参数中给出方案限定的操作符名称，请使用OPERATOR() 语法，例如：

  1. COMMUTATOR = OPERATOR(myschema.===) ,

注意

任何实现该操作符的函数必须定义成 IMMUTABLE。

列子

下面创建一个操作符来加两个复数的例子，假设我们已经创建了 复数类型。 首先定义执行工作的函数，然后定义操作符：

  1. CREATE FUNCTION complex_add(complex, complex)
  2.     RETURNS complex
  3.     AS 'filename', 'complex_add'
  4.     LANGUAGE C IMMUTABLE STRICT;
  5. CREATE OPERATOR + (
  6.     leftarg = complex,
  7.     rightarg = complex,
  8.     procedure = complex_add,
  9.     commutator = +
  10. );

在查询中使用操作符：

  1. SELECT (a + b) AS c FROM test_complex;

兼容性

CREATE OPERATOR 是Greenplum数据库的语言扩展。 SQL标准没有提供用户自定义操作符。

另见

CREATE FUNCTION, CREATE TYPE, ALTER OPERATOR, DROP OPERATOR

上级话题： SQL命令参考