PolarDB:ALTER OPERATOR FAMILY

Description

Use ALTER OPERATOR FAMILY to add or remove operators and support functions, rename the family, change its owner, or move it to a different schema.

Operators and support functions added with ALTER OPERATOR FAMILY become "loose" members of the family — they are not part of any specific operator class within it. This means they are compatible with the family's semantics but are not required for any specific index to function correctly. Unlike operator class members, loose members can be dropped from the family at any time without affecting existing indexes. Operators and functions that an index requires for correct operation should be declared as part of an operator class instead (see CREATE OPERATOR CLASS).

In practice, single-data-type operators and functions are typically part of operator classes because they are needed to support an index on a specific data type, while cross-data-type operators and functions are made loose members of the family.

Only a superuser can run ALTER OPERATOR FAMILY. An erroneous operator family definition can confuse or crash the server.

ALTER OPERATOR FAMILY does not validate that the operator family includes all required operators and functions, or that they form a self-consistent set. Defining a valid operator family is the user's responsibility.

Synopsis

ALTER OPERATOR FAMILY name USING index_method ADD
      {  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 [, ...] ) ]
      } [, ... ]

ALTER OPERATOR FAMILY name USING index_method DROP
      {  OPERATOR strategy_number ( op_type [ , op_type ] )
       | FUNCTION support_number ( op_type [ , op_type ] )
      } [, ... ]

ALTER OPERATOR FAMILY name USING index_method
        RENAME TO new_name

ALTER OPERATOR FAMILY name USING index_method
        OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER OPERATOR FAMILY name USING index_method
        SET SCHEMA new_schema

Parameters

name

The name (optionally schema-qualified) of an existing operator family.

index_method

The name of the index method this operator family is for.

strategy_number

The index method's strategy number for an operator associated with the operator family.

operator_name

The name (optionally schema-qualified) of an operator associated with the operator family.

op_type

The meaning of op_type depends on which clause it appears in:

• OPERATOR clause: The operand data type(s) of the operator, or NONE to signify a prefix operator. Unlike CREATE OPERATOR CLASS, the operand data types must always be specified.

• ADD FUNCTION clause: The operand data type(s) the function is intended to support, if different from the function's input data type(s). For B-tree comparison functions and hash functions, op_type is not required because the function's input data type(s) are always the correct ones to use. For B-tree sort support functions, B-tree equal image functions, and all functions in GiST, SP-GiST, and GIN operator classes, op_type must be specified.

• DROP FUNCTION clause: The operand data type(s) the function is intended to support must be specified.

sort_family_name

The name (optionally schema-qualified) of an existing B-tree 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 a function associated with the operator family.

function_name

The name (optionally schema-qualified) of a function that is an index method support function for the operator family. If no argument list is specified, the name must be unique in its schema.

argument_type

The parameter data type(s) of the function.

new_name

The new name of the operator family.

new_owner

The new owner of the operator family.

new_schema

The new schema for the operator family.

The OPERATOR and FUNCTION clauses can appear in any order.

Usage notes

The DROP syntax identifies entries by their "slot" — the strategy or support number plus input data type(s) — not by the operator or function name. For DROP FUNCTION, specify the data type(s) the function is intended to support. For GiST, SP-GiST, and GIN indexes, these may differ from the function's actual input argument types.

Adding a function or operator to an operator family grants public execute permission on it. The index machinery does not check access permissions on functions before using them. This is generally not an issue for the kinds of functions useful in operator families.

Do not define operators using SQL functions. A SQL function is likely to be inlined into the calling query, which prevents the optimizer from recognizing that the query matches an index.

Examples

The following example adds cross-data-type operators and support functions to an operator family that already contains B-tree operator classes for the int4 and int2 data types. Operators are identified by strategy number, and support functions by support number.

ALTER OPERATOR FAMILY integer_ops USING btree ADD

      -- int4 vs int2
      OPERATOR 1 < (int4, int2) ,
      OPERATOR 2 <= (int4, int2) ,
      OPERATOR 3 = (int4, int2) ,
      OPERATOR 4 >= (int4, int2) ,
      OPERATOR 5 > (int4, int2) ,
      FUNCTION 1 btint42cmp(int4, int2) ,

      -- int2 vs int4
      OPERATOR 1 < (int2, int4) ,
      OPERATOR 2 <= (int2, int4) ,
      OPERATOR 3 = (int2, int4) ,
      OPERATOR 4 >= (int2, int4) ,
      OPERATOR 5 > (int2, int4) ,
      FUNCTION 1 btint24cmp(int2, int4) ;

To remove these entries, specify each slot by strategy or support number and data types — not by name:

ALTER OPERATOR FAMILY integer_ops USING btree DROP

      -- int4 vs int2
      OPERATOR 1 (int4, int2) ,
      OPERATOR 2 (int4, int2) ,
      OPERATOR 3 (int4, int2) ,
      OPERATOR 4 (int4, int2) ,
      OPERATOR 5 (int4, int2) ,
      FUNCTION 1 (int4, int2) ,

      -- int2 vs int4
      OPERATOR 1 (int2, int4) ,
      OPERATOR 2 (int2, int4) ,
      OPERATOR 3 (int2, int4) ,
      OPERATOR 4 (int2, int4) ,
      OPERATOR 5 (int2, int4) ,
      FUNCTION 1 (int2, int4) ;

See also

• CREATE OPERATOR CLASS