'\" t .\" Title: CREATE OPERATOR .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 16.3 Documentation .\" Source: PostgreSQL 16.3 .\" Language: English .\" .TH "CREATE OPERATOR" "7" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 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))