3.22. Operators

Operators are one of the most powerful objects implemented by PostgreSQL as an extension of the SQL standard. Despite the enormous set of built-in operators available in a default installation, the user is able to 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 an user defined function. Additionally, operators can be overloaded, meaning that the same operator can handle different set of data types.

Its quite simple to create an operator but there are some rules that must be observed in order to avoid errors. First, operator names should be composed by one or more of the following characters +-*/ <> =~!@#%^&|‘? as stated by the documentation. Second, one or two argument data types must be specified. Finally, the function that defines the operator must have a specific signature according to the arguments types.

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 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 on 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 estimation function for the operator.
Restrict This object picker defines a function to be used as the restriction selectively function 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

Jan 3, 2020 at 12:33