CREATE OPERATOR CLASS

Description

Defines a new operator class. This function is for internal use only. You are advised not to use it.

Precautions

• CREATE OPERATOR CLASS defines a new operator class. An operator class defines how to use a specified data type together with an index. The operator class declares that certain operators will provide particular roles or "strategies" for this data type and this index method. The operator class also declares the supported programs used by the index method when the operator class is selected for an index column. All the operators and functions used by an operator class must be defined before the operator class is created.
• If a schema name is given, then the operator class is created in the specified schema. Otherwise, it is created in the current schema. Two operator classes in the same schema can have the same name only if they are for different index methods.
• The user who defines an operator class becomes the owner. Currently, the creator must be a super user.
• CREATE OPERATOR CLASS does not check whether the class definition includes all the operators and functions required by the index method, nor whether the operators and functions form a self-contained set.
• Related operator classes can be grouped into operator families. To add a new operator class to an existing family, specify a FAMILY option in CREATE OPERATOR CLASS. Without this option, the new class is placed into a family with the same name (a family is created if it does not 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
} [, ... ]

Parameters

• name

  Specifies the name of the operator class to be created (which can be schema-qualified).

• default

  Specifies that the operator class will become the default operator class for its data type. At most one operator class can be the default for a specific data type and index method.

• data_type

  Specifies the column data type processed by the operator class.

• index_method

  Specifies the name of the index method processed by the operator class.

• family_name

  Specifies the name of an existing operator family where an operator class is added. If not specified, the class is placed into a family with the same name (a family is created if it does not exist).

• strategy_number

  Specifies the strategy number of the index method associated with the operator class.

• operator_name

  Specifies the name (which can be schema-qualified) of the operator associated with the operator class.

• op_type

  Specifies the data type of the operator's operand in an OPERATOR clause. The value NONE represents a left unary operator or right unary operator. The data type of the operand can be omitted if it is the same as that of the operator class.

  In a FUNCTION clause, if the data type of the function's operand is different from the input data type of the function (such as B-tree comparison functions and hash functions) or the class's data type, the data type of the operand supported by this function must be included in this clause. These default values are correct. Therefore, op_type need not be specified in FUNCTION clauses, except in cases where the B-tree sort support function supports cross-type comparisons.

• sort_family_name

  Specifies the name of an existing B-tree operator family that describes the sort ordering associated with a sort operator.

  The default value is FOR SEARCH.

• support_number

  Specifies the number of an index method for a function associated with the operator class.

• function_name

  Specifies a function name of an index method for an operator class.

• argument_type

  Specifies a data type of a function parameter.

• storage_type

  Specifies the data type stored in the index. Normally, this is the same as the column data type, but some index methods allow it to be different. The STORAGE clause must be omitted unless the index method allows a different type to be used.

Examples

-- Define a function.
gaussdb=# CREATE FUNCTION func_add_sql(num1 integer, num2 integer) RETURN integer AS BEGIN
RETURN num1 + num2; 
END;
/
-- Create an operator class and associate it with the preceding function.
gaussdb=# CREATE OPERATOR CLASS oc1 DEFAULT FOR TYPE _int4 USING btree AS FUNCTION 1 func_add_sql (integer, integer);