'\" t .\" Title: CREATE OPERATOR .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "CREATE OPERATOR" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * 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 ( 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 use of => as an operator name is deprecated\&. It may be disallowed altogether in a future release\&. .RE .sp .RE .PP The operator != is mapped to <> on input, so these two names are always equivalent\&. .PP At least one of LEFTARG and RIGHTARG must be defined\&. For binary operators, both must be defined\&. For right unary operators, only LEFTARG should be defined, while for left unary operators only RIGHTARG should be defined\&. .PP The \fIfunction_name\fR procedure 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 The other clauses specify optional operator optimization clauses\&. Their meaning is detailed in Section 35.13, \(lqOperator Optimization Information\(rq, in the documentation\&. .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 left\-unary operator\&. .RE .PP \fIright_type\fR .RS 4 The data type of the operator\(aqs right operand, if any\&. This option would be omitted for a right\-unary operator\&. .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 35.12, \(lqUser-defined Operators\(rq, in the documentation 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, \(lqOperator Precedence\(rq, in the documentation 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 DROP OPERATOR (\fBDROP_OPERATOR\fR(5)) to delete user\-defined operators from a database\&. Use ALTER OPERATOR (\fBALTER_OPERATOR\fR(5)) 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, PROCEDURE = area_equal_procedure, COMMUTATOR = ===, NEGATOR = !==, RESTRICT = area_restriction_procedure, JOIN = area_join_procedure, 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(5)), CREATE OPERATOR CLASS (\fBCREATE_OPERATOR_CLASS\fR(5)), DROP OPERATOR (\fBDROP_OPERATOR\fR(5))