Debian Codegraph @

doc/src/sgml/ref/create_opclass.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_opclass.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createopclass">
7 <indexterm zone="sql-createopclass">
8 <primary>CREATE OPERATOR CLASS</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE OPERATOR CLASS</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE OPERATOR CLASS</refname>
19 <refpurpose>define a new operator class</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable>
25 USING <replaceable class="parameter">index_method</replaceable> [ FAMILY <replaceable class="parameter">family_name</replaceable> ] AS
26 { 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> ) ] [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ]
27 | FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">function_name</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )
28 | STORAGE <replaceable class="parameter">storage_type</replaceable>
29 } [, ... ]
30 </synopsis>
31 </refsynopsisdiv>
32
33 <refsect1>
34 <title>Description</title>
35
36 <para>
37 <command>CREATE OPERATOR CLASS</command> creates a new operator class.
38 An operator class defines how a particular data type can be used with
39 an index. The operator class specifies that certain operators will fill
40 particular roles or <quote>strategies</quote> for this data type and this
41 index method. The operator class also specifies the support functions to
42 be used by
43 the index method when the operator class is selected for an
44 index column. All the operators and functions used by an operator
45 class must be defined before the operator class can be created.
46 </para>
47
48 <para>
49 If a schema name is given then the operator class is created in the
50 specified schema. Otherwise it is created in the current schema.
51 Two operator classes in the same schema can have the same name only if they
52 are for different index methods.
53 </para>
54
55 <para>
56 The user who defines an operator class becomes its owner. Presently,
57 the creating user must be a superuser. (This restriction is made because
58 an erroneous operator class definition could confuse or even crash the
59 server.)
60 </para>
61
62 <para>
63 <command>CREATE OPERATOR CLASS</command> does not presently check
64 whether the operator class definition includes all the operators and
65 functions required by the index method, nor whether the operators and
66 functions form a self-consistent set. It is the user's
67 responsibility to define a valid operator class.
68 </para>
69
70 <para>
71 Related operator classes can be grouped into <firstterm>operator
72 families</firstterm>. To add a new operator class to an existing family,
73 specify the <literal>FAMILY</literal> option in <command>CREATE OPERATOR
74 CLASS</command>. Without this option, the new class is placed into
75 a family named the same as the new class (creating that family if
76 it doesn't already exist).
77 </para>
78
79 <para>
80 Refer to <xref linkend="xindex"/> for further information.
81 </para>
82 </refsect1>
83
84 <refsect1>
85 <title>Parameters</title>
86
87 <variablelist>
88 <varlistentry>
89 <term><replaceable class="parameter">name</replaceable></term>
90 <listitem>
91 <para>
92 The name of the operator class to be created. The name can be
93 schema-qualified.
94 </para>
95 </listitem>
96 </varlistentry>
97
98 <varlistentry>
99 <term><literal>DEFAULT</literal></term>
100 <listitem>
101 <para>
102 If present, the operator class will become the default
103 operator class for its data type. At most one operator class
104 can be the default for a specific data type and index method.
105 </para>
106 </listitem>
107 </varlistentry>
108
109 <varlistentry>
110 <term><replaceable class="parameter">data_type</replaceable></term>
111 <listitem>
112 <para>
113 The column data type that this operator class is for.
114 </para>
115 </listitem>
116 </varlistentry>
117
118 <varlistentry>
119 <term><replaceable class="parameter">index_method</replaceable></term>
120 <listitem>
121 <para>
122 The name of the index method this operator class is for.
123 </para>
124 </listitem>
125 </varlistentry>
126
127 <varlistentry>
128 <term><replaceable class="parameter">family_name</replaceable></term>
129 <listitem>
130 <para>
131 The name of the existing operator family to add this operator class to.
132 If not specified, a family named the same as the operator class is
133 used (creating it, if it doesn't already exist).
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><replaceable class="parameter">strategy_number</replaceable></term>
140 <listitem>
141 <para>
142 The index method's strategy number for an operator
143 associated with the operator class.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><replaceable class="parameter">operator_name</replaceable></term>
150 <listitem>
151 <para>
152 The name (optionally schema-qualified) of an operator associated
153 with the operator class.
154 </para>
155 </listitem>
156 </varlistentry>
157
158 <varlistentry>
159 <term><replaceable class="parameter">op_type</replaceable></term>
160 <listitem>
161 <para>
162 In an <literal>OPERATOR</literal> clause,
163 the operand data type(s) of the operator, or <literal>NONE</literal> to
164 signify a prefix operator. The operand data
165 types can be omitted in the normal case where they are the same
166 as the operator class's data type.
167 </para>
168
169 <para>
170 In a <literal>FUNCTION</literal> clause, the operand data type(s) the
171 function is intended to support, if different from
172 the input data type(s) of the function (for B-tree comparison functions
173 and hash functions)
174 or the class's data type (for B-tree sort support functions,
175 B-tree equal image functions, and all functions in GiST,
176 SP-GiST, GIN and BRIN operator classes). These defaults are
177 correct, and so <replaceable
178 class="parameter">op_type</replaceable> need not be specified
179 in <literal>FUNCTION</literal> clauses, except for the case of a
180 B-tree sort support function that is meant to support
181 cross-data-type comparisons.
182 </para>
183 </listitem>
184 </varlistentry>
185
186 <varlistentry>
187 <term><replaceable class="parameter">sort_family_name</replaceable></term>
188 <listitem>
189 <para>
190 The name (optionally schema-qualified) of an existing <literal>btree</literal> operator
191 family that describes the sort ordering associated with an ordering
192 operator.
193 </para>
194
195 <para>
196 If neither <literal>FOR SEARCH</literal> nor <literal>FOR ORDER BY</literal> is
197 specified, <literal>FOR SEARCH</literal> is the default.
198 </para>
199 </listitem>
200 </varlistentry>
201
202 <varlistentry>
203 <term><replaceable class="parameter">support_number</replaceable></term>
204 <listitem>
205 <para>
206 The index method's support function number for a
207 function associated with the operator class.
208 </para>
209 </listitem>
210 </varlistentry>
211
212 <varlistentry>
213 <term><replaceable class="parameter">function_name</replaceable></term>
214 <listitem>
215 <para>
216 The name (optionally schema-qualified) of a function that is an
217 index method support function for the operator class.
218 </para>
219 </listitem>
220 </varlistentry>
221
222 <varlistentry>
223 <term><replaceable class="parameter">argument_type</replaceable></term>
224 <listitem>
225 <para>
226 The parameter data type(s) of the function.
227 </para>
228 </listitem>
229 </varlistentry>
230
231 <varlistentry>
232 <term><replaceable class="parameter">storage_type</replaceable></term>
233 <listitem>
234 <para>
235 The data type actually stored in the index. Normally this is
236 the same as the column data type, but some index methods
237 (currently GiST, GIN, SP-GiST and BRIN) allow it to be different. The
238 <literal>STORAGE</literal> clause must be omitted unless the index
239 method allows a different type to be used.
240 If the column <replaceable class="parameter">data_type</replaceable> is specified
241 as <type>anyarray</type>, the <replaceable class="parameter">storage_type</replaceable>
242 can be declared as <type>anyelement</type> to indicate that the index
243 entries are members of the element type belonging to the actual array
244 type that each particular index is created for.
245 </para>
246 </listitem>
247 </varlistentry>
248 </variablelist>
249
250 <para>
251 The <literal>OPERATOR</literal>, <literal>FUNCTION</literal>, and <literal>STORAGE</literal>
252 clauses can appear in any order.
253 </para>
254 </refsect1>
255
256 <refsect1>
257 <title>Notes</title>
258
259 <para>
260 Because the index machinery does not check access permissions on functions
261 before using them, including a function or operator in an operator class
262 is tantamount to granting public execute permission on it. This is usually
263 not an issue for the sorts of functions that are useful in an operator
264 class.
265 </para>
266
267 <para>
268 The operators should not be defined by SQL functions. An SQL function
269 is likely to be inlined into the calling query, which will prevent
270 the optimizer from recognizing that the query matches an index.
271 </para>
272 </refsect1>
273
274 <refsect1>
275 <title>Examples</title>
276
277 <para>
278 The following example command defines a GiST index operator class
279 for the data type <literal>_int4</literal> (array of <type>int4</type>). See the
280 <xref linkend="intarray"/> module for the complete example.
281 </para>
282
283 <programlisting>
284 CREATE OPERATOR CLASS gist__int_ops
285 DEFAULT FOR TYPE _int4 USING gist AS
286 OPERATOR 3 &amp;&amp;,
287 OPERATOR 6 = (anyarray, anyarray),
288 OPERATOR 7 @&gt;,
289 OPERATOR 8 &lt;@,
290 OPERATOR 20 @@ (_int4, query_int),
291 FUNCTION 1 g_int_consistent (internal, _int4, smallint, oid, internal),
292 FUNCTION 2 g_int_union (internal, internal),
293 FUNCTION 3 g_int_compress (internal),
294 FUNCTION 4 g_int_decompress (internal),
295 FUNCTION 5 g_int_penalty (internal, internal, internal),
296 FUNCTION 6 g_int_picksplit (internal, internal),
297 FUNCTION 7 g_int_same (_int4, _int4, internal);
298 </programlisting>
299 </refsect1>
300
301 <refsect1>
302 <title>Compatibility</title>
303
304 <para>
305 <command>CREATE OPERATOR CLASS</command> is a
306 <productname>PostgreSQL</productname> extension. There is no
307 <command>CREATE OPERATOR CLASS</command> statement in the SQL
308 standard.
309 </para>
310 </refsect1>
311
312 <refsect1>
313 <title>See Also</title>
314
315 <simplelist type="inline">
316 <member><xref linkend="sql-alteropclass"/></member>
317 <member><xref linkend="sql-dropopclass"/></member>
318 <member><xref linkend="sql-createopfamily"/></member>
319 <member><xref linkend="sql-alteropfamily"/></member>
320 </simplelist>
321 </refsect1>
322 </refentry>