Create operator class statement - PolarDB

CREATE OPERATOR CLASS defines a new operator class. An operator class controls how a specific data type can be indexed.

Overview

An operator class binds a data type to an index method by specifying:

Which operators fill particular strategies (roles) for that data type and index method
Which support functions the index method calls when the operator class is selected for an index column

Before creating an operator class, define all operators and functions it will use.

Schema placement: If you provide a schema name, the operator class is created in that schema; otherwise it is created in the current schema. Two operator classes in the same schema may share a name only if they target different index methods.

Required privilege: The user who defines an operator class becomes its owner. Only a superuser can create an operator class. This restriction exists because an invalid operator class definition can confuse or crash the server.

CREATE OPERATOR CLASS does not verify that the definition includes all operators and functions required by the index method, or that they form a self-consistent set. Defining a valid operator class is your responsibility.

Operator families

Related operator classes can be grouped into an operator family. To add a new operator class to an existing family, use the FAMILY option. Without FAMILY, the new class is placed in a family that shares its name—creating that family if it does not already exist.

Syntax

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
  USING index_method [ FAMILY family_name ] AS
  {  OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ FOR SEARCH | FOR ORDER BY sort_family_name ]
   | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] )
   | STORAGE storage_type
  } [, ... ]

The OPERATOR, FUNCTION, and STORAGE clauses can appear in any order.

Parameters

Parameter	Description
`name`	The name of the operator class. Can be schema-qualified.
`DEFAULT`	Makes this operator class the default for its data type. At most one operator class can be the default for a specific data type and index method.
`data_type`	The column data type that this operator class is for.
`index_method`	The name of the index method this operator class is for (for example, `btree`, `gist`, `gin`).
`family_name`	The name of an existing operator family to add this operator class to. If omitted, the class is placed in a family with the same name (created if it does not exist).
`strategy_number`	The index method's strategy number for the associated operator.
`operator_name`	The name (optionally schema-qualified) of an operator associated with the operator class.
`op_type` (in `OPERATOR`)	The operand data type(s) of the operator, or `NONE` to indicate a prefix operator. Omit when the operand types match the operator class's data type.
`op_type` (in `FUNCTION`)	The operand data type(s) the function is intended to support, if different from the function's input data types (for B-tree comparison functions and hash functions) or the class's data type (for B-tree sort support functions, B-tree equal image functions, and all functions in GiST, SP-GiST, GIN, and BRIN operator classes). These defaults are correct in most cases, so `op_type` does not need to be specified in `FUNCTION` clauses—except for a B-tree sort support function meant to support cross-data-type comparisons.
`sort_family_name`	The name (optionally schema-qualified) of an existing `btree` operator family that describes the sort ordering associated with an ordering operator. If neither `FOR SEARCH` nor `FOR ORDER BY` is specified, `FOR SEARCH` is the default.
`support_number`	The index method's support function number for the associated function.
`function_name`	The name (optionally schema-qualified) of an index method support function.
`argument_type`	The parameter data type(s) of the function.
`storage_type`	The data type actually stored in the index. Normally the same as the column data type. GiST, GIN, SP-GiST, and BRIN allow a different storage type. Omit the `STORAGE` clause unless the index method supports an alternative type. If `data_type` is `anyarray`, `storage_type` can be declared as `anyelement` to indicate that index entries are members of the element type of the actual array type.

Usage notes

Access permissions: The index machinery does not check access permissions on functions before using them. Including a function or operator in an operator class effectively grants public execute permission on it.
Avoid SQL functions for operators: Define operators using non-SQL functions. SQL functions are likely to be inlined into the calling query, which prevents the optimizer from recognizing that the query can use an index.

Example

The following statement defines a GiST index operator class for the _int4 data type (array of int4).

CREATE OPERATOR CLASS gist__int_ops
    DEFAULT FOR TYPE _int4 USING gist AS
        OPERATOR        3       &&,
        OPERATOR        6       = (anyarray, anyarray),
        OPERATOR        7       @>,
        OPERATOR        8       <@,
        OPERATOR        20      @@ (_int4, query_int),
        FUNCTION        1       g_int_consistent (internal, _int4, smallint, oid, internal),
        FUNCTION        2       g_int_union (internal, internal),
        FUNCTION        3       g_int_compress (internal),
        FUNCTION        4       g_int_decompress (internal),
        FUNCTION        5       g_int_penalty (internal, internal, internal),
        FUNCTION        6       g_int_picksplit (internal, internal),
        FUNCTION        7       g_int_same (_int4, _int4, internal);

What's next

ALTER OPERATOR CLASS — modify an existing operator class
DROP OPERATOR CLASS — remove an operator class
CREATE OPERATOR FAMILY — create an operator family
ALTER OPERATOR FAMILY — add or remove operators and functions in an operator family