diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createopclass.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createopclass.html | 152 |
1 files changed, 152 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createopclass.html b/doc/src/sgml/html/sql-createopclass.html new file mode 100644 index 0000000..f42dd6f --- /dev/null +++ b/doc/src/sgml/html/sql-createopclass.html @@ -0,0 +1,152 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE OPERATOR CLASS</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createoperator.html" title="CREATE OPERATOR" /><link rel="next" href="sql-createopfamily.html" title="CREATE OPERATOR FAMILY" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE OPERATOR CLASS</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createoperator.html" title="CREATE OPERATOR">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createopfamily.html" title="CREATE OPERATOR FAMILY">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEOPCLASS"><div class="titlepage"></div><a id="id-1.9.3.73.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE OPERATOR CLASS</span></h2><p>CREATE OPERATOR CLASS — define a new operator class</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE OPERATOR CLASS <em class="replaceable"><code>name</code></em> [ DEFAULT ] FOR TYPE <em class="replaceable"><code>data_type</code></em> + USING <em class="replaceable"><code>index_method</code></em> [ FAMILY <em class="replaceable"><code>family_name</code></em> ] AS + { OPERATOR <em class="replaceable"><code>strategy_number</code></em> <em class="replaceable"><code>operator_name</code></em> [ ( <em class="replaceable"><code>op_type</code></em>, <em class="replaceable"><code>op_type</code></em> ) ] [ FOR SEARCH | FOR ORDER BY <em class="replaceable"><code>sort_family_name</code></em> ] + | FUNCTION <em class="replaceable"><code>support_number</code></em> [ ( <em class="replaceable"><code>op_type</code></em> [ , <em class="replaceable"><code>op_type</code></em> ] ) ] <em class="replaceable"><code>function_name</code></em> ( <em class="replaceable"><code>argument_type</code></em> [, ...] ) + | STORAGE <em class="replaceable"><code>storage_type</code></em> + } [, ... ] +</pre></div><div class="refsect1" id="id-1.9.3.73.5"><h2>Description</h2><p> + <code class="command">CREATE OPERATOR CLASS</code> 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 <span class="quote">“<span class="quote">strategies</span>”</span> for this data type and this + index method. The operator class also specifies the support functions 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. + </p><p> + 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. + </p><p> + 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 + server.) + </p><p> + <code class="command">CREATE OPERATOR CLASS</code> 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. + </p><p> + Related operator classes can be grouped into <em class="firstterm">operator + families</em>. To add a new operator class to an existing family, + specify the <code class="literal">FAMILY</code> option in <code class="command">CREATE OPERATOR + CLASS</code>. 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). + </p><p> + Refer to <a class="xref" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Section 38.16</a> for further information. + </p></div><div class="refsect1" id="id-1.9.3.73.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name of the operator class to be created. The name can be + schema-qualified. + </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p> + 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. + </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p> + The column data type that this operator class is for. + </p></dd><dt><span class="term"><em class="replaceable"><code>index_method</code></em></span></dt><dd><p> + The name of the index method this operator class is for. + </p></dd><dt><span class="term"><em class="replaceable"><code>family_name</code></em></span></dt><dd><p> + 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). + </p></dd><dt><span class="term"><em class="replaceable"><code>strategy_number</code></em></span></dt><dd><p> + The index method's strategy number for an operator + associated with the operator class. + </p></dd><dt><span class="term"><em class="replaceable"><code>operator_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of an operator associated + with the operator class. + </p></dd><dt><span class="term"><em class="replaceable"><code>op_type</code></em></span></dt><dd><p> + In an <code class="literal">OPERATOR</code> clause, + the operand data type(s) of the operator, or <code class="literal">NONE</code> to + signify a prefix operator. The operand data + types can be omitted in the normal case where they are the same + as the operator class's data type. + </p><p> + In a <code class="literal">FUNCTION</code> 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 comparison functions + and hash functions) + or the class's data type (for B-tree sort support functions, + B-tree equal image functions, and all functions in GiST, + SP-GiST, GIN and BRIN operator classes). These defaults are + correct, and so <em class="replaceable"><code>op_type</code></em> need not be specified + in <code class="literal">FUNCTION</code> clauses, except for the case of a + B-tree sort support function that is meant to support + cross-data-type comparisons. + </p></dd><dt><span class="term"><em class="replaceable"><code>sort_family_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of an existing <code class="literal">btree</code> operator + family that describes the sort ordering associated with an ordering + operator. + </p><p> + If neither <code class="literal">FOR SEARCH</code> nor <code class="literal">FOR ORDER BY</code> is + specified, <code class="literal">FOR SEARCH</code> is the default. + </p></dd><dt><span class="term"><em class="replaceable"><code>support_number</code></em></span></dt><dd><p> + The index method's support function number for a + function associated with the operator class. + </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a function that is an + index method support function for the operator class. + </p></dd><dt><span class="term"><em class="replaceable"><code>argument_type</code></em></span></dt><dd><p> + The parameter data type(s) of the function. + </p></dd><dt><span class="term"><em class="replaceable"><code>storage_type</code></em></span></dt><dd><p> + The data type actually stored in the index. Normally this is + the same as the column data type, but some index methods + (currently GiST, GIN, SP-GiST and BRIN) allow it to be different. The + <code class="literal">STORAGE</code> clause must be omitted unless the index + method allows a different type to be used. + If the column <em class="replaceable"><code>data_type</code></em> is specified + as <code class="type">anyarray</code>, the <em class="replaceable"><code>storage_type</code></em> + can be declared as <code class="type">anyelement</code> to indicate that the index + entries are members of the element type belonging to the actual array + type that each particular index is created for. + </p></dd></dl></div><p> + The <code class="literal">OPERATOR</code>, <code class="literal">FUNCTION</code>, and <code class="literal">STORAGE</code> + clauses can appear in any order. + </p></div><div class="refsect1" id="id-1.9.3.73.7"><h2>Notes</h2><p> + Because the index machinery does not check access permissions on functions + before using them, including a function or operator in an operator class + is tantamount to granting public execute permission on it. This is usually + not an issue for the sorts of functions that are useful in an operator + class. + </p><p> + The operators should not be defined by SQL functions. An SQL function + is likely to be inlined into the calling query, which will prevent + the optimizer from recognizing that the query matches an index. + </p><p> + Before <span class="productname">PostgreSQL</span> 8.4, the <code class="literal">OPERATOR</code> + clause could include a <code class="literal">RECHECK</code> option. This is no longer + supported because whether an index operator is <span class="quote">“<span class="quote">lossy</span>”</span> is now + determined on-the-fly at run time. This allows efficient handling of + cases where an operator might or might not be lossy. + </p></div><div class="refsect1" id="id-1.9.3.73.8"><h2>Examples</h2><p> + The following example command defines a GiST index operator class + for the data type <code class="literal">_int4</code> (array of <code class="type">int4</code>). See the + <a class="xref" href="intarray.html" title="F.20. intarray">intarray</a> module for the complete example. + </p><pre class="programlisting"> +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, smallint, 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); +</pre></div><div class="refsect1" id="id-1.9.3.73.9"><h2>Compatibility</h2><p> + <code class="command">CREATE OPERATOR CLASS</code> is a + <span class="productname">PostgreSQL</span> extension. There is no + <code class="command">CREATE OPERATOR CLASS</code> statement in the SQL + standard. + </p></div><div class="refsect1" id="id-1.9.3.73.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alteropclass.html" title="ALTER OPERATOR CLASS"><span class="refentrytitle">ALTER OPERATOR CLASS</span></a>, <a class="xref" href="sql-dropopclass.html" title="DROP OPERATOR CLASS"><span class="refentrytitle">DROP OPERATOR CLASS</span></a>, <a class="xref" href="sql-createopfamily.html" title="CREATE OPERATOR FAMILY"><span class="refentrytitle">CREATE OPERATOR FAMILY</span></a>, <a class="xref" href="sql-alteropfamily.html" title="ALTER OPERATOR FAMILY"><span class="refentrytitle">ALTER OPERATOR FAMILY</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createoperator.html" title="CREATE OPERATOR">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createopfamily.html" title="CREATE OPERATOR FAMILY">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE OPERATOR </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE OPERATOR FAMILY</td></tr></table></div></body></html>
\ No newline at end of file |