Man Pages

create_operator_class(7) - phpMan create_operator_class(7) - phpMan

Command: man perldoc info search(apropos)  


       CREATE OPERATOR CLASS - define a new operator class

         USING index_method [ FAMILY family_name ] AS
         {  OPERATOR strategy_number operator_name [ ( op_type, op_type ) ]
          | FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
          | STORAGE storage_type
         } [, ... ]

       CREATE  OPERATOR  CLASS creates a new operator class.  An operator class defines how a particular data type can
       be used with an index. The operator class specifies that  certain  operators  will  fill  particular  roles  or
       ''strategies''  for  this data type and this index method. The operator class also specifies the support proce-
       dures to be 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 can be 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 its owner. Presently, the creating user must be a superuser.
       (This restriction is made because an erroneous operator class  definition  could  confuse  or  even  crash  the

       CREATE OPERATOR CLASS does not presently check whether the operator class definition includes all the operators
       and functions required by the index method, nor whether the operators and functions form a self-consistent set.
       It is the user's responsibility to define a valid operator class.

       Related operator classes can be grouped into operator families. To add a new operator class to an existing fam-
       ily, specify the FAMILY option in CREATE OPERATOR CLASS. Without this option, the new class is  placed  into  a
       family named the same as the new class (creating that family if it doesn't already exist).

       Refer to in the documentation for further information.

       name   The name of the operator class to be created. The name can be schema-qualified.

              If  present,  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.

              The column data type that this operator class is for.

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

              The name of the existing operator family to add this operator class to.   If  not  specified,  a  family
              named the same as the operator class is used (creating it, if it doesn't already exist).

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

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

              In  an  OPERATOR  clause,  the  operand data type(s) of the operator, or NONE to signify a left-unary or
              right-unary operator. The operand data types can be omitted in the normal case where they are  the  same
              as the operator class's data type.

              In  a  FUNCTION  clause, the operand data type(s) the function is intended to support, if different from
              the input data type(s) of the function (for B-tree and hash indexes) or the class's data type  (for  GIN
              and  GiST  indexes).  These defaults are always correct, so there is no point in specifying op_type in a
              FUNCTION clause in CREATE OPERATOR CLASS, but the option is provided for consistency with the comparable
              syntax in ALTER OPERATOR FAMILY.

              The index method's support procedure number for a function associated with the operator class.

              The  name  (optionally schema-qualified) of a function that is an index method support procedure for the
              operator class.

              The parameter data type(s) of the function.

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

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

       Because the index machinery does not check access permissions on functions before using them, including a func-
       tion  or  operator in an operator class is tantamount to granting public execute permission on it. This is usu-
       ally not an issue for the sorts of functions that are useful in an operator class.

       The operators should not be defined by SQL functions. A SQL function is likely to be inlined into  the  calling
       query, which will prevent the optimizer from recognizing that the query matches an index.

       Before  PostgreSQL 8.4, the OPERATOR clause could include a RECHECK option. This is no longer supported because
       whether an index operator is ''lossy'' is now determined on-the-fly at runtime. This allows efficient  handling
       of cases where an operator might or might not be lossy.

       The  following example command defines a GiST index operator class for the data type _int4 (array of int4). See
       contrib/intarray/ for the complete example.

       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, int, 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);

       CREATE OPERATOR CLASS is a PostgreSQL extension. There is no CREATE OPERATOR CLASS statement in the  SQL  stan-

       ALTER  OPERATOR  CLASS [alter_operator_class(7)], DROP OPERATOR CLASS [drop_operator_class(7)], CREATE OPERATOR
       FAMILY [create_operator_family(7)], ALTER OPERATOR FAMILY [alter_operator_family(7)]

SQL - Language Statements         2014-02-17          CREATE OPERATOR CLASS(7)