In PostgreSQL, a custom operator allows you to create your own SQL operator symbol.
CREATE OPERATOR defines a new operator, name. 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.
The operator name is a sequence of up to NAMEDATALEN-1 (63 by default) characters from the following list:
+ - * / < > = ~ ! @ # % ^ & | ` ?
Normally SQL uses built-in operators:
= <> < > LIKE
For binary operators, both LEFTARG and RIGHTARG must be defined.
For prefix operators only RIGHTARG should be defined. The function_name function must have been previously defined using CREATE FUNCTION and must be defined to accept the correct number of arguments (either one or two) of the indicated types.
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.
PostgreSQL allows creating new ones:
~=~
<@> &&&
Operator internally calls a function.
Example:
account_no ~=~'ACC-100'
internally becomes:
account_match(account_no, 'ACC-100')
This allows expressing business rules directly in SQL syntax.
Real business problem:
Different systems send same account number in different formats:
