Postgres Custom operator with examples

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

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:

ACC-000100
acc 000100
ACC_000100
ACC/000100
acc.000100

Business says:

all represent same account

Normal SQL says:

all are different strings

Custom operator solves this elegantly.

postgres_custom_operator.sql