Postgresql 中文操作指南

CREATE OPERATOR

CREATE OPERATOR — 定义新操作符

CREATE OPERATOR — define a new operator

Synopsis

CREATE OPERATOR name (
    {FUNCTION|PROCEDURE} = function_name
    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
    [, RESTRICT = res_proc ] [, JOIN = join_proc ]
    [, HASHES ] [, MERGES ]
)

Description

CREATE OPERATOR 定义了一个新操作符 name 。定义操作符的用户成为其所有者。如果给出了架构名称，则在指定的架构中创建操作符。否则，在当前架构中创建它。

CREATE OPERATOR defines a new operator, name. The user who defines an operator becomes its owner. If a schema name is given then the operator is created in the specified schema. Otherwise it is created in the current schema.

操作符名称是从以下列表中最多 NAMEDATALEN -1（默认情况下为 63）个字符的序列：

The operator name is a sequence of up to NAMEDATALEN-1 (63 by default) characters from the following list:

+ - * / < > = ~ ! @ # % ^ & | ` ?

+ - * / < > = ~ ! @ # % ^ & | ` ?

对于你的姓名选择有一些限制：

There are a few restrictions on your choice of name:

~ ! @ # % ^ & | ` ?

~ ! @ # % ^ & | ` ?

在输入中，操作符 != 映射到 <> ，所以这两个名称总是等效的。

The operator != is mapped to <> on input, so these two names are always equivalent.

对于二元操作符， LEFTARG 和 RIGHTARG 必须都已定义。对于前缀操作符，仅 RIGHTARG 应该已定义。 function_name 函数必须已经使用 CREATE FUNCTION 进行了定义，且必须定义为接受指定类型的正确数量（一或两个）的参数。

For binary operators, both LEFTARG and RIGHTARG must be defined. For prefix operators only RIGHTARG should be defined. The function_name function must have been previously defined using CREATE FUNCTION and must be defined to accept the correct number of arguments (either one or two) of the indicated types.

在 CREATE OPERATOR 语法中，关键字 FUNCTION 和 PROCEDURE 是等效的，但是引用的函数必须在任何情况下都是函数，而不是过程。这里使用关键字 PROCEDURE 是历史遗留，已被弃用。

In the syntax of CREATE OPERATOR, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

其他子句指定可选操作符优化子句。它们的含义在 Section 38.15 中有详细说明。

The other clauses specify optional operator optimization clauses. Their meaning is detailed in Section 38.15.

若要能够创建操作符，你必须具有对参数类型和返回类型的 USAGE 权限，以及对基础函数的 EXECUTE 权限。如果指定了交换器或否定器操作符，你必须拥有这些操作符。

To be able to create an operator, you must have USAGE privilege on the argument types and the return type, as well as EXECUTE privilege on the underlying function. If a commutator or negator operator is specified, you must own these operators.

Parameters

  • name

    • The name of the operator to be defined. See above for allowable characters. The name can be schema-qualified, for example CREATE OPERATOR myschema.+ (…​). If not, then the operator is created in the current schema. Two operators in the same schema can have the same name if they operate on different data types. This is called overloading.

  • function_name

    • The function used to implement this operator.

  • left_type

    • The data type of the operator’s left operand, if any. This option would be omitted for a prefix operator.

  • right_type

    • The data type of the operator’s right operand.

  • com_op

    • The commutator of this operator.

  • neg_op

    • The negator of this operator.

  • res_proc

    • The restriction selectivity estimator function for this operator.

  • join_proc

    • The join selectivity estimator function for this operator.

  • HASHES

    • Indicates this operator can support a hash join.

  • MERGES

    • Indicates this operator can support a merge join.

要在 com_op 或其他可选参数中给出模式限定的操作符名称，请使用 OPERATOR() 语法，例如：

To give a schema-qualified operator name in com_op or the other optional arguments, use the OPERATOR() syntax, for example:

COMMUTATOR = OPERATOR(myschema.===) ,

Notes

有关其他信息，请参阅 Section 38.14 。

Refer to Section 38.14 for further information.

无法在 CREATE OPERATOR 中指定操作符的词法优先级，因为解析器的优先级行为是硬编码的。有关优先级详情，请参阅 Section 4.1.6 。

It is not possible to specify an operator’s lexical precedence in CREATE OPERATOR, because the parser’s precedence behavior is hard-wired. See Section 4.1.6 for precedence details.

过时的选项 SORT1 、 SORT2 、 LTCMP 和 GTCMP 以前用于指定与可合并联接操作符关联的排序操作符的名称。现在这不再需要，因为有关关联操作符的信息是通过查看 B 树操作符族来查找的。如果给出了其中一个选项，除了隐式设置 MERGES 为 true 之外，它将被忽略。

The obsolete options SORT1, SORT2, LTCMP, and GTCMP were formerly used to specify the names of sort operators associated with a merge-joinable operator. This is no longer necessary, since information about associated operators is found by looking at B-tree operator families instead. If one of these options is given, it is ignored except for implicitly setting MERGES true.

使用 ALTER OPERATOR 在数据库中删除用户自定义运算符。使用 DROP OPERATOR 修改数据库中的运算符。

Use DROP OPERATOR to delete user-defined operators from a database. Use ALTER OPERATOR to modify operators in a database.

Examples

以下命令为数据类型 ALTER OPERATOR 定义了一个新运算符，area-equality：

The following command defines a new operator, area-equality, for the data type box:

CREATE OPERATOR === (
    LEFTARG = box,
    RIGHTARG = box,
    FUNCTION = area_equal_function,
    COMMUTATOR = ===,
    NEGATOR = !==,
    RESTRICT = area_restriction_function,
    JOIN = area_join_function,
    HASHES, MERGES
);

Compatibility

box 是一个 PostgreSQL 扩展。在 SQL 标准中没有规定用户自定义运算符。

CREATE OPERATOR is a PostgreSQL extension. There are no provisions for user-defined operators in the SQL standard.

See Also

CREATE OPERATOR ， ALTER OPERATOR ， CREATE OPERATOR CLASS

ALTER OPERATOR, CREATE OPERATOR CLASS, DROP OPERATOR