diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_OPERATOR_CLASS.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_OPERATOR_CLASS.7 | 223 |
1 files changed, 223 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_OPERATOR_CLASS.7 b/doc/src/sgml/man7/CREATE_OPERATOR_CLASS.7 new file mode 100644 index 0000000..d0b9b3d --- /dev/null +++ b/doc/src/sgml/man7/CREATE_OPERATOR_CLASS.7 @@ -0,0 +1,223 @@ +'\" t +.\" Title: CREATE OPERATOR CLASS +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "CREATE OPERATOR CLASS" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +CREATE_OPERATOR_CLASS \- define a new operator class +.SH "SYNOPSIS" +.sp +.nf +CREATE OPERATOR CLASS \fIname\fR [ DEFAULT ] FOR TYPE \fIdata_type\fR + USING \fIindex_method\fR [ FAMILY \fIfamily_name\fR ] AS + { OPERATOR \fIstrategy_number\fR \fIoperator_name\fR [ ( \fIop_type\fR, \fIop_type\fR ) ] [ FOR SEARCH | FOR ORDER BY \fIsort_family_name\fR ] + | FUNCTION \fIsupport_number\fR [ ( \fIop_type\fR [ , \fIop_type\fR ] ) ] \fIfunction_name\fR ( \fIargument_type\fR [, \&.\&.\&.] ) + | STORAGE \fIstorage_type\fR + } [, \&.\&.\&. ] +.fi +.SH "DESCRIPTION" +.PP +\fBCREATE OPERATOR CLASS\fR +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 +\(lqstrategies\(rq +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\&. +.PP +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\&. +.PP +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\&.) +.PP +\fBCREATE OPERATOR CLASS\fR +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\*(Aqs responsibility to define a valid operator class\&. +.PP +Related operator classes can be grouped into +operator families\&. To add a new operator class to an existing family, specify the +FAMILY +option in +\fBCREATE OPERATOR CLASS\fR\&. Without this option, the new class is placed into a family named the same as the new class (creating that family if it doesn\*(Aqt already exist)\&. +.PP +Refer to +Section\ \&38.16 +for further information\&. +.SH "PARAMETERS" +.PP +\fIname\fR +.RS 4 +The name of the operator class to be created\&. The name can be schema\-qualified\&. +.RE +.PP +DEFAULT +.RS 4 +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\&. +.RE +.PP +\fIdata_type\fR +.RS 4 +The column data type that this operator class is for\&. +.RE +.PP +\fIindex_method\fR +.RS 4 +The name of the index method this operator class is for\&. +.RE +.PP +\fIfamily_name\fR +.RS 4 +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\*(Aqt already exist)\&. +.RE +.PP +\fIstrategy_number\fR +.RS 4 +The index method\*(Aqs strategy number for an operator associated with the operator class\&. +.RE +.PP +\fIoperator_name\fR +.RS 4 +The name (optionally schema\-qualified) of an operator associated with the operator class\&. +.RE +.PP +\fIop_type\fR +.RS 4 +In an +OPERATOR +clause, the operand data type(s) of the operator, or +NONE +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\*(Aqs data type\&. +.sp +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 comparison functions and hash functions) or the class\*(Aqs 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 +\fIop_type\fR +need not be specified in +FUNCTION +clauses, except for the case of a B\-tree sort support function that is meant to support cross\-data\-type comparisons\&. +.RE +.PP +\fIsort_family_name\fR +.RS 4 +The name (optionally schema\-qualified) of an existing +btree +operator family that describes the sort ordering associated with an ordering operator\&. +.sp +If neither +FOR SEARCH +nor +FOR ORDER BY +is specified, +FOR SEARCH +is the default\&. +.RE +.PP +\fIsupport_number\fR +.RS 4 +The index method\*(Aqs support function number for a function associated with the operator class\&. +.RE +.PP +\fIfunction_name\fR +.RS 4 +The name (optionally schema\-qualified) of a function that is an index method support function for the operator class\&. +.RE +.PP +\fIargument_type\fR +.RS 4 +The parameter data type(s) of the function\&. +.RE +.PP +\fIstorage_type\fR +.RS 4 +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 +STORAGE +clause must be omitted unless the index method allows a different type to be used\&. If the column +\fIdata_type\fR +is specified as +anyarray, the +\fIstorage_type\fR +can be declared as +anyelement +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\&. +.RE +.PP +The +OPERATOR, +FUNCTION, and +STORAGE +clauses can appear in any order\&. +.SH "NOTES" +.PP +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\&. +.PP +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\&. +.PP +Before +PostgreSQL +8\&.4, the +OPERATOR +clause could include a +RECHECK +option\&. This is no longer supported because whether an index operator is +\(lqlossy\(rq +is now determined on\-the\-fly at run time\&. This allows efficient handling of cases where an operator might or might not be lossy\&. +.SH "EXAMPLES" +.PP +The following example command defines a GiST index operator class for the data type +_int4 +(array of +int4)\&. See the +intarray +module for the complete example\&. +.sp +.if n \{\ +.RS 4 +.\} +.nf +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); +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +\fBCREATE OPERATOR CLASS\fR +is a +PostgreSQL +extension\&. There is no +\fBCREATE OPERATOR CLASS\fR +statement in the SQL standard\&. +.SH "SEE ALSO" +ALTER OPERATOR CLASS (\fBALTER_OPERATOR_CLASS\fR(7)), DROP OPERATOR CLASS (\fBDROP_OPERATOR_CLASS\fR(7)), CREATE OPERATOR FAMILY (\fBCREATE_OPERATOR_FAMILY\fR(7)), ALTER OPERATOR FAMILY (\fBALTER_OPERATOR_FAMILY\fR(7)) |