2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_opclass.sgml,v 1.18 2006/10/16 17:28:03 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEOPCLASS">
8 <refentrytitle id="sql-createopclass-title">CREATE OPERATOR CLASS</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE OPERATOR CLASS</refname>
14 <refpurpose>define a new operator class</refpurpose>
17 <indexterm zone="sql-createopclass">
18 <primary>CREATE OPERATOR CLASS</primary>
23 CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable> USING <replaceable class="parameter">index_method</replaceable> AS
24 { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> [ ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) ] [ RECHECK ]
25 | FUNCTION <replaceable class="parameter">support_number</replaceable> <replaceable class="parameter">funcname</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )
26 | STORAGE <replaceable class="parameter">storage_type</replaceable>
32 <title>Description</title>
35 <command>CREATE OPERATOR CLASS</command> creates a new operator class.
36 An operator class defines how a particular data type can be used with
37 an index. The operator class specifies that certain operators will fill
38 particular roles or <quote>strategies</> for this data type and this
39 index method. The operator class also specifies the support procedures to
41 the index method when the operator class is selected for an
42 index column. All the operators and functions used by an operator
43 class must be defined before the operator class is created.
47 If a schema name is given then the operator class is created in the
48 specified schema. Otherwise it is created in the current schema.
49 Two operator classes in the same schema can have the same name only if they
50 are for different index methods.
54 The user who defines an operator class becomes its owner. Presently,
55 the creating user must be a superuser. (This restriction is made because
56 an erroneous operator class definition could confuse or even crash the
61 <command>CREATE OPERATOR CLASS</command> does not presently check
62 whether the operator class definition includes all the operators and
63 functions required by the index method, nor whether the operators and
64 functions form a self-consistent set. It is the user's
65 responsibility to define a valid operator class.
69 Refer to <xref linkend="xindex"> for further information.
74 <title>Parameters</title>
78 <term><replaceable class="parameter">name</replaceable></term>
81 The name of the operator class to be created. The name may be
88 <term><literal>DEFAULT</></term>
91 If present, the operator class will become the default
92 operator class for its data type. At most one operator class
93 can be the default for a specific data type and index method.
99 <term><replaceable class="parameter">data_type</replaceable></term>
102 The column data type that this operator class is for.
108 <term><replaceable class="parameter">index_method</replaceable></term>
111 The name of the index method this operator class is for.
117 <term><replaceable class="parameter">strategy_number</replaceable></term>
120 The index method's strategy number for an operator
121 associated with the operator class.
127 <term><replaceable class="parameter">operator_name</replaceable></term>
130 The name (optionally schema-qualified) of an operator associated
131 with the operator class.
137 <term><replaceable class="parameter">op_type</replaceable></term>
140 The operand data type(s) of an operator, or <literal>NONE</> to
141 signify a left-unary or right-unary operator. The operand data
142 types may be omitted in the normal case where they are the same
143 as the operator class's data type.
149 <term><literal>RECHECK</></term>
152 If present, the index is <quote>lossy</> for this operator, and
153 so the rows retrieved using the index must be rechecked to
154 verify that they actually satisfy the qualification clause
155 involving this operator.
161 <term><replaceable class="parameter">support_number</replaceable></term>
164 The index method's support procedure number for a
165 function associated with the operator class.
171 <term><replaceable class="parameter">funcname</replaceable></term>
174 The name (optionally schema-qualified) of a function that is an
175 index method support procedure for the operator class.
181 <term><replaceable class="parameter">argument_types</replaceable></term>
184 The parameter data type(s) of the function.
190 <term><replaceable class="parameter">storage_type</replaceable></term>
193 The data type actually stored in the index. Normally this is
194 the same as the column data type, but some index methods
195 (GIN and GiST for now) allow it to be different. The
196 <literal>STORAGE</> clause must be omitted unless the index
197 method allows a different type to be used.
204 The <literal>OPERATOR</>, <literal>FUNCTION</>, and <literal>STORAGE</>
205 clauses may appear in any order.
213 Because the index machinery does not check access permissions on functions
214 before using them, including a function or operator in an operator class
215 is tantamount to granting public execute permission on it. This is usually
216 not an issue for the sorts of functions that are useful in an operator
221 The operators should not be defined by SQL functions. A SQL function
222 is likely to be inlined into the calling query, which will prevent
223 the optimizer from recognizing that the query matches an index.
228 <title>Examples</title>
231 The following example command defines a GiST index operator class
232 for the data type <literal>_int4</> (array of <type>int4</type>). See
233 <filename>contrib/intarray/</> for the complete example.
237 CREATE OPERATOR CLASS gist__int_ops
238 DEFAULT FOR TYPE _int4 USING gist AS
240 OPERATOR 6 = RECHECK,
243 OPERATOR 20 @@ (_int4, query_int),
244 FUNCTION 1 g_int_consistent (internal, _int4, int4),
245 FUNCTION 2 g_int_union (bytea, internal),
246 FUNCTION 3 g_int_compress (internal),
247 FUNCTION 4 g_int_decompress (internal),
248 FUNCTION 5 g_int_penalty (internal, internal, internal),
249 FUNCTION 6 g_int_picksplit (internal, internal),
250 FUNCTION 7 g_int_same (_int4, _int4, internal);
255 <title>Compatibility</title>
258 <command>CREATE OPERATOR CLASS</command> is a
259 <productname>PostgreSQL</productname> extension. There is no
260 <command>CREATE OPERATOR CLASS</command> statement in the SQL
266 <title>See Also</title>
268 <simplelist type="inline">
269 <member><xref linkend="sql-alteropclass" endterm="sql-alteropclass-title"></member>
270 <member><xref linkend="sql-dropopclass" endterm="sql-dropopclass-title"></member>