Define a new operator with CREATE OPERATOR - PolarDB

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 defines a new operator named name. The user who defines an operator becomes its owner. If a schema name is specified, the operator is created in that schema; otherwise, it is created in the current schema.

Operator name rules

An operator name is a sequence of up to NAMEDATALEN-1 characters (63 by default) chosen from:

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

Three restrictions apply:

-- and /* cannot appear anywhere in an operator name, because the parser treats them as comment starts.
A multicharacter operator name cannot end in + or - unless it also contains at least one of ~ ! @ # % ^ & | ?. For example, @- is valid but *-` is not. This restriction lets PolarDB parse SQL-compliant commands without requiring spaces between tokens.
=> is reserved by the SQL grammar and cannot be used as an operator name.

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

Argument requirements

Binary operators: both LEFTARG and RIGHTARG must be defined.
Prefix operators: only RIGHTARG should be defined.
Suffix operators: only LEFTARG should be defined.

The right unary operator (also known as a suffix operator) is deprecated.

Parameters

Parameter	Description
`name`	The name of the operator. Can be schema-qualified, for example `CREATE OPERATOR myschema.+ (...)`. Two operators in the same schema can share the same name if they operate on different data types (overloading).
`function_name`	The function that implements this operator. Must be defined in advance with `CREATE FUNCTION` and must accept the correct number of arguments of the specified types.
`left_type`	The data type of the operator's left operand. Omit this 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.

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

COMMUTATOR = OPERATOR(myschema.===),

Usage notes

Privileges

To create an operator, you need:

USAGE privilege on the argument types and the return type
EXECUTE privilege on the underlying function
Ownership of any commutator or negator operators you specify

FUNCTION and PROCEDURE keywords

In the CREATE OPERATOR syntax, FUNCTION and PROCEDURE are interchangeable, but the referenced object must be a function, not a procedure. The PROCEDURE keyword is historical and deprecated.

Lexical precedence

Operator lexical precedence cannot be set in CREATE OPERATOR. The parser's precedence behavior is hard-wired.

Obsolete options

The options SORT1, SORT2, LTCMP, and GTCMP are obsolete. If specified, they are ignored except that they implicitly set MERGES to true. Associated sort operators are now found through B-tree operator families instead.

Examples

The following example defines an area-equality operator (===) for the box data type:

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

PolarDB:CREATE OPERATOR