diff options
Diffstat (limited to 'doc/src/sgml/man7/CREATE_OPERATOR.7')
| -rw-r--r-- | doc/src/sgml/man7/CREATE_OPERATOR.7 | 282 |
1 files changed, 282 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/CREATE_OPERATOR.7 b/doc/src/sgml/man7/CREATE_OPERATOR.7 new file mode 100644 index 0000000..235f688 --- /dev/null +++ b/doc/src/sgml/man7/CREATE_OPERATOR.7 @@ -0,0 +1,282 @@ +'\" t +.\" Title: CREATE OPERATOR +.\" 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" "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 \- define a new operator +.SH "SYNOPSIS" +.sp +.nf +CREATE OPERATOR \fIname\fR ( + {FUNCTION|PROCEDURE} = \fIfunction_name\fR + [, LEFTARG = \fIleft_type\fR ] [, RIGHTARG = \fIright_type\fR ] + [, COMMUTATOR = \fIcom_op\fR ] [, NEGATOR = \fIneg_op\fR ] + [, RESTRICT = \fIres_proc\fR ] [, JOIN = \fIjoin_proc\fR ] + [, HASHES ] [, MERGES ] +) +.fi +.SH "DESCRIPTION" +.PP +\fBCREATE OPERATOR\fR +defines a new operator, +\fIname\fR\&. The user who defines an operator becomes its owner\&. If a schema name is given then the operator is created in the specified schema\&. Otherwise it is created in the current schema\&. +.PP +The operator name is a sequence of up to +NAMEDATALEN\-1 (63 by default) characters from the following list: +.sp +.if n \{\ +.RS 4 +.\} +.nf ++ \- * / < > = ~ ! @ # % ^ & | ` ? +.fi +.if n \{\ +.RE +.\} +.sp +There are a few restrictions on your choice of name: +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +\-\- +and +/* +cannot appear anywhere in an operator name, since they will be taken as the start of a comment\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +A multicharacter operator name cannot end in ++ +or +\-, unless the name also contains at least one of these characters: +.sp +.if n \{\ +.RS 4 +.\} +.nf +~ ! @ # % ^ & | ` ? +.fi +.if n \{\ +.RE +.\} +.sp +For example, +@\- +is an allowed operator name, but +*\- +is not\&. This restriction allows +PostgreSQL +to parse SQL\-compliant commands without requiring spaces between tokens\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04'\(bu\h'+03'\c +.\} +.el \{\ +.sp -1 +.IP \(bu 2.3 +.\} +The symbol +=> +is reserved by the SQL grammar, so it cannot be used as an operator name\&. +.RE +.PP +The operator +!= +is mapped to +<> +on input, so these two names are always equivalent\&. +.PP +For binary operators, both +LEFTARG +and +RIGHTARG +must be defined\&. For prefix operators only +RIGHTARG +should be defined\&. The +\fIfunction_name\fR +function must have been previously defined using +\fBCREATE FUNCTION\fR +and must be defined to accept the correct number of arguments (either one or two) of the indicated types\&. +.PP +In the syntax of +CREATE OPERATOR, the keywords +FUNCTION +and +PROCEDURE +are equivalent, but the referenced function must in any case be a function, not a procedure\&. The use of the keyword +PROCEDURE +here is historical and deprecated\&. +.PP +The other clauses specify optional operator optimization clauses\&. Their meaning is detailed in +Section\ \&38.15\&. +.PP +To be able to create an operator, you must have +USAGE +privilege on the argument types and the return type, as well as +EXECUTE +privilege on the underlying function\&. If a commutator or negator operator is specified, you must own these operators\&. +.SH "PARAMETERS" +.PP +\fIname\fR +.RS 4 +The name of the operator to be defined\&. See above for allowable characters\&. The name can be schema\-qualified, for example +CREATE OPERATOR myschema\&.+ (\&.\&.\&.)\&. If not, then the operator is created in the current schema\&. Two operators in the same schema can have the same name if they operate on different data types\&. This is called +overloading\&. +.RE +.PP +\fIfunction_name\fR +.RS 4 +The function used to implement this operator\&. +.RE +.PP +\fIleft_type\fR +.RS 4 +The data type of the operator\*(Aqs left operand, if any\&. This option would be omitted for a prefix operator\&. +.RE +.PP +\fIright_type\fR +.RS 4 +The data type of the operator\*(Aqs right operand\&. +.RE +.PP +\fIcom_op\fR +.RS 4 +The commutator of this operator\&. +.RE +.PP +\fIneg_op\fR +.RS 4 +The negator of this operator\&. +.RE +.PP +\fIres_proc\fR +.RS 4 +The restriction selectivity estimator function for this operator\&. +.RE +.PP +\fIjoin_proc\fR +.RS 4 +The join selectivity estimator function for this operator\&. +.RE +.PP +HASHES +.RS 4 +Indicates this operator can support a hash join\&. +.RE +.PP +MERGES +.RS 4 +Indicates this operator can support a merge join\&. +.RE +.PP +To give a schema\-qualified operator name in +\fIcom_op\fR +or the other optional arguments, use the +OPERATOR() +syntax, for example: +.sp +.if n \{\ +.RS 4 +.\} +.nf +COMMUTATOR = OPERATOR(myschema\&.===) , +.fi +.if n \{\ +.RE +.\} +.SH "NOTES" +.PP +Refer to +Section\ \&38.14 +for further information\&. +.PP +It is not possible to specify an operator\*(Aqs lexical precedence in +\fBCREATE OPERATOR\fR, because the parser\*(Aqs precedence behavior is hard\-wired\&. See +Section\ \&4.1.6 +for precedence details\&. +.PP +The obsolete options +SORT1, +SORT2, +LTCMP, and +GTCMP +were formerly used to specify the names of sort operators associated with a merge\-joinable operator\&. This is no longer necessary, since information about associated operators is found by looking at B\-tree operator families instead\&. If one of these options is given, it is ignored except for implicitly setting +MERGES +true\&. +.PP +Use +\fBDROP OPERATOR\fR +to delete user\-defined operators from a database\&. Use +\fBALTER OPERATOR\fR +to modify operators in a database\&. +.SH "EXAMPLES" +.PP +The following command defines a new operator, area\-equality, for the data type +box: +.sp +.if n \{\ +.RS 4 +.\} +.nf +CREATE OPERATOR === ( + LEFTARG = box, + RIGHTARG = box, + FUNCTION = area_equal_function, + COMMUTATOR = ===, + NEGATOR = !==, + RESTRICT = area_restriction_function, + JOIN = area_join_function, + HASHES, MERGES +); +.fi +.if n \{\ +.RE +.\} +.SH "COMPATIBILITY" +.PP +\fBCREATE OPERATOR\fR +is a +PostgreSQL +extension\&. There are no provisions for user\-defined operators in the SQL standard\&. +.SH "SEE ALSO" +ALTER OPERATOR (\fBALTER_OPERATOR\fR(7)), CREATE OPERATOR CLASS (\fBCREATE_OPERATOR_CLASS\fR(7)), DROP OPERATOR (\fBDROP_OPERATOR\fR(7)) |