3.22. Operators
Operators are one of the most powerful objects implemented by PostgreSQL as an extension of the SQL standards. Despite the enormous set of built-in operators available in a default installation, the user can create custom operators as done for functions and languages, for instance.
An operator can be unary or binary depending on the arguments specified for it, moreover, all the logic of this object is stored in a user-defined function. Additionally, operators can be overloaded, meaning that the same operator can handle a different set of data types.
It's quite simple to create an operator but some rules must be observed to avoid errors. First, operator names should be composed of one or more of the following characters +-*/ <> =~!@#%^&|‘? as stated by the documentation. Second, one or two data types must be specified as arguments of the operator. Finally, the function that defines the operator must have a specific signature according to the types of arguments.
| Attribute | Description |
|---|---|
Operator Function |
Defines the function that will perform the operator's logic. This function must have the signature: void function(typeA, typeB) where typeA is the same as the one configured in the Left Argument Type field and typeB the same type as Right Argument Type. |
Options |
Options used for the operator. MERGES indicates that the operator supports a merge join. HASHES indicates that the operator supports a hash join. Those options are fully detailed in PostgreSQL documentation. |
Left Argument Type |
Configures the left argument data type. If you intend to create a left unary operator just configure this field with the any data type. |
Right Argument Type |
Configures the right argument data type. If you intend to create a right unary operator just configure this field with the any data type. |
Advanced |
This tab reunites fields used as advanced options for the operator being configured. |
Join |
This object picker defines a function to be used as an estimation function for the operator. |
Restrict |
This object picker defines a function to be used as the restriction selectively functions for the operator. |
Commutator |
This object picker defines a previously configured operator to be used as the commutator for the object being configured. |
Negator |
This object picker defines a previously configured operator to be used as the negator for the object being configured. |
Operator DDL
https://www.postgresql.org/docs/current/static/sql-createoperator.html
Operator Optimization
https://www.postgresql.org/docs/current/static/xoper-optimization.html