1CREATE OPERATOR CLASS(7) SQL Commands CREATE OPERATOR CLASS(7)
2
6 CREATE OPERATOR CLASS - define a new operator class
7
10 CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
11 USING index_method [ FAMILY family_name ] AS
12 { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ]
13 | FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
14 | STORAGE storage_type
15 } [, ... ]
16
19 CREATE OPERATOR CLASS creates a new operator class. An operator class
20 defines how a particular data type can be used with an index. The oper‐
21 ator class specifies that certain operators will fill particular roles
22 or ``strategies'' for this data type and this index method. The opera‐
23 tor class also specifies the support procedures to be used by the index
24 method when the operator class is selected for an index column. All the
25 operators and functions used by an operator class must be defined
26 before the operator class can be created.
27 If a schema name is given then the operator class is created in the
29 specified schema. Otherwise it is created in the current schema. Two
30 operator classes in the same schema can have the same name only if they
31 are for different index methods.
32 The user who defines an operator class becomes its owner. Presently,
34 the creating user must be a superuser. (This restriction is made
35 because an erroneous operator class definition could confuse or even
36 crash the server.)
37 CREATE OPERATOR CLASS does not presently check whether the operator
39 class definition includes all the operators and functions required by
40 the index method, nor whether the operators and functions form a self-
41 consistent set. It is the user's responsibility to define a valid oper‐
42 ator class.
43 Related operator classes can be grouped into operator families. To add
45 a new operator class to an existing family, specify the FAMILY option
46 in CREATE OPERATOR CLASS. Without this option, the new class is placed
47 into a family named the same as the new class (creating that family if
48 it doesn't already exist).
49 Refer to in the documentation for further information.
51
53 name The name of the operator class to be created. The name can be
54 schema-qualified.
55 DEFAULT
57 If present, the operator class will become the default operator
58 class for its data type. At most one operator class can be the
59 default for a specific data type and index method.
60 data_type
62 The column data type that this operator class is for.
63 index_method
65 The name of the index method this operator class is for.
66 family_name
68 The name of the existing operator family to add this operator
69 class to. If not specified, a family named the same as the
70 operator class is used (creating it, if it doesn't already
71 exist).
72 strategy_number
74 The index method's strategy number for an operator associated
75 with the operator class.
76 operator_name
78 The name (optionally schema-qualified) of an operator associated
79 with the operator class.
80 op_type
82 In an OPERATOR clause, the operand data type(s) of the operator,
83 or NONE to signify a left-unary or right-unary operator. The op‐
84 erand data types can be omitted in the normal case where they
85 are the same as the operator class's data type.
86 In a FUNCTION clause, the operand data type(s) the function is
88 intended to support, if different from the input data type(s) of
89 the function (for B-tree and hash indexes) or the class's data
90 type (for GIN and GiST indexes). These defaults are always cor‐
91 rect, so there is no point in specifying op_type in a FUNCTION
92 clause in CREATE OPERATOR CLASS, but the option is provided for
93 consistency with the comparable syntax in ALTER OPERATOR FAMILY.
94 support_number
96 The index method's support procedure number for a function asso‐
97 ciated with the operator class.
98 funcname
100 The name (optionally schema-qualified) of a function that is an
101 index method support procedure for the operator class.
102 argument_types
104 The parameter data type(s) of the function.
105 storage_type
107 The data type actually stored in the index. Normally this is the
108 same as the column data type, but some index methods (currently
109 GIN and GiST) allow it to be different. The STORAGE clause must
110 be omitted unless the index method allows a different type to be
111 used.
112 The OPERATOR, FUNCTION, and STORAGE clauses can appear in any order.
114
116 Because the index machinery does not check access permissions on func‐
117 tions before using them, including a function or operator in an opera‐
118 tor class is tantamount to granting public execute permission on it.
119 This is usually not an issue for the sorts of functions that are useful
120 in an operator class.
121 The operators should not be defined by SQL functions. A SQL function is
123 likely to be inlined into the calling query, which will prevent the
124 optimizer from recognizing that the query matches an index.
125 Before PostgreSQL 8.4, the OPERATOR clause could include a RECHECK
127 option. This is no longer supported because whether an index operator
128 is ``lossy'' is now determined on-the-fly at runtime. This allows effi‐
129 cient handling of cases where an operator might or might not be lossy.
130
132 The following example command defines a GiST index operator class for
133 the data type _int4 (array of int4). See contrib/intarray/ for the com‐
134 plete example.
135 CREATE OPERATOR CLASS gist__int_ops
137 DEFAULT FOR TYPE _int4 USING gist AS
138 OPERATOR 3 &&,
139 OPERATOR 6 = (anyarray, anyarray),
140 OPERATOR 7 @>,
141 OPERATOR 8 <@,
142 OPERATOR 20 @@ (_int4, query_int),
143 FUNCTION 1 g_int_consistent (internal, _int4, int, oid, internal),
144 FUNCTION 2 g_int_union (internal, internal),
145 FUNCTION 3 g_int_compress (internal),
146 FUNCTION 4 g_int_decompress (internal),
147 FUNCTION 5 g_int_penalty (internal, internal, internal),
148 FUNCTION 6 g_int_picksplit (internal, internal),
149 FUNCTION 7 g_int_same (_int4, _int4, internal);
150
153 CREATE OPERATOR CLASS is a PostgreSQL extension. There is no CREATE
154 OPERATOR CLASS statement in the SQL standard.
155
157 ALTER OPERATOR CLASS [alter_operator_class(7)], DROP OPERATOR CLASS
158 [drop_operator_class(7)], CREATE OPERATOR FAMILY [create_operator_fam‐
159 ily(7)], ALTER OPERATOR FAMILY [alter_operator_family(7)]
160SQL - Language Statements 2011-09-22 CREATE OPERATOR CLASS(7)