1
<!-- doc/src/sgml/xoper.sgml -->
4
<title>User-defined Operators</title>
6
<indexterm zone="xoper">
7
<primary>operator</primary>
8
<secondary>user-defined</secondary>
12
Every operator is <quote>syntactic sugar</quote> for a call to an
13
underlying function that does the real work; so you must
14
first create the underlying function before you can create
15
the operator. However, an operator is <emphasis>not merely</emphasis>
16
syntactic sugar, because it carries additional information
17
that helps the query planner optimize queries that use the
18
operator. The next section will be devoted to explaining
19
that additional information.
23
<productname>PostgreSQL</productname> supports left unary, right
24
unary, and binary operators. Operators can be
25
overloaded;<indexterm><primary>overloading</primary><secondary>operators</secondary></indexterm>
26
that is, the same operator name can be used for different operators
27
that have different numbers and types of operands. When a query is
28
executed, the system determines the operator to call from the
29
number and types of the provided operands.
33
Here is an example of creating an operator for adding two complex
34
numbers. We assume we've already created the definition of type
35
<type>complex</type> (see <xref linkend="xtypes">). First we need a
36
function that does the work, then we can define the operator:
39
CREATE FUNCTION complex_add(complex, complex)
41
AS '<replaceable>filename</replaceable>', 'complex_add'
42
LANGUAGE C IMMUTABLE STRICT;
47
procedure = complex_add,
54
Now we could execute a query like this:
57
SELECT (a + b) AS c FROM test_complex;
67
We've shown how to create a binary operator here. To create unary
68
operators, just omit one of <literal>leftarg</> (for left unary) or
69
<literal>rightarg</> (for right unary). The <literal>procedure</>
70
clause and the argument clauses are the only required items in
71
<command>CREATE OPERATOR</command>. The <literal>commutator</>
72
clause shown in the example is an optional hint to the query
73
optimizer. Further details about <literal>commutator</> and other
74
optimizer hints appear in the next section.
78
<sect1 id="xoper-optimization">
79
<title>Operator Optimization Information</title>
82
A <productname>PostgreSQL</productname> operator definition can include
83
several optional clauses that tell the system useful things about how
84
the operator behaves. These clauses should be provided whenever
85
appropriate, because they can make for considerable speedups in execution
86
of queries that use the operator. But if you provide them, you must be
87
sure that they are right! Incorrect use of an optimization clause can
88
result in slow queries, subtly wrong output, or other Bad Things.
89
You can always leave out an optimization clause if you are not sure
90
about it; the only consequence is that queries might run slower than
95
Additional optimization clauses might be added in future versions of
96
<productname>PostgreSQL</productname>. The ones described here are all
97
the ones that release &version; understands.
101
<title><literal>COMMUTATOR</></title>
104
The <literal>COMMUTATOR</> clause, if provided, names an operator that is the
105
commutator of the operator being defined. We say that operator A is the
106
commutator of operator B if (x A y) equals (y B x) for all possible input
107
values x, y. Notice that B is also the commutator of A. For example,
108
operators <literal><</> and <literal>></> for a particular data type are usually each others'
109
commutators, and operator <literal>+</> is usually commutative with itself.
110
But operator <literal>-</> is usually not commutative with anything.
114
The left operand type of a commutable operator is the same as the
115
right operand type of its commutator, and vice versa. So the name of
116
the commutator operator is all that <productname>PostgreSQL</productname>
117
needs to be given to look up the commutator, and that's all that needs to
118
be provided in the <literal>COMMUTATOR</> clause.
122
It's critical to provide commutator information for operators that
123
will be used in indexes and join clauses, because this allows the
124
query optimizer to <quote>flip around</> such a clause to the forms
125
needed for different plan types. For example, consider a query with
126
a WHERE clause like <literal>tab1.x = tab2.y</>, where <literal>tab1.x</>
127
and <literal>tab2.y</> are of a user-defined type, and suppose that
128
<literal>tab2.y</> is indexed. The optimizer cannot generate an
129
index scan unless it can determine how to flip the clause around to
130
<literal>tab2.y = tab1.x</>, because the index-scan machinery expects
131
to see the indexed column on the left of the operator it is given.
132
<productname>PostgreSQL</productname> will <emphasis>not</> simply
133
assume that this is a valid transformation — the creator of the
134
<literal>=</> operator must specify that it is valid, by marking the
135
operator with commutator information.
139
When you are defining a self-commutative operator, you just do it.
140
When you are defining a pair of commutative operators, things are
141
a little trickier: how can the first one to be defined refer to the
142
other one, which you haven't defined yet? There are two solutions
148
One way is to omit the <literal>COMMUTATOR</> clause in the first operator that
149
you define, and then provide one in the second operator's definition.
150
Since <productname>PostgreSQL</productname> knows that commutative
151
operators come in pairs, when it sees the second definition it will
152
automatically go back and fill in the missing <literal>COMMUTATOR</> clause in
153
the first definition.
159
The other, more straightforward way is just to include <literal>COMMUTATOR</> clauses
160
in both definitions. When <productname>PostgreSQL</productname> processes
161
the first definition and realizes that <literal>COMMUTATOR</> refers to a nonexistent
162
operator, the system will make a dummy entry for that operator in the
163
system catalog. This dummy entry will have valid data only
164
for the operator name, left and right operand types, and result type,
165
since that's all that <productname>PostgreSQL</productname> can deduce
166
at this point. The first operator's catalog entry will link to this
167
dummy entry. Later, when you define the second operator, the system
168
updates the dummy entry with the additional information from the second
169
definition. If you try to use the dummy operator before it's been filled
170
in, you'll just get an error message.
178
<title><literal>NEGATOR</></title>
181
The <literal>NEGATOR</> clause, if provided, names an operator that is the
182
negator of the operator being defined. We say that operator A
183
is the negator of operator B if both return Boolean results and
184
(x A y) equals NOT (x B y) for all possible inputs x, y.
185
Notice that B is also the negator of A.
186
For example, <literal><</> and <literal>>=</> are a negator pair for most data types.
187
An operator can never validly be its own negator.
191
Unlike commutators, a pair of unary operators could validly be marked
192
as each others' negators; that would mean (A x) equals NOT (B x)
193
for all x, or the equivalent for right unary operators.
197
An operator's negator must have the same left and/or right operand types
198
as the operator to be defined, so just as with <literal>COMMUTATOR</>, only the operator
199
name need be given in the <literal>NEGATOR</> clause.
203
Providing a negator is very helpful to the query optimizer since
204
it allows expressions like <literal>NOT (x = y)</> to be simplified into
205
<literal>x <> y</>. This comes up more often than you might think, because
206
<literal>NOT</> operations can be inserted as a consequence of other rearrangements.
210
Pairs of negator operators can be defined using the same methods
211
explained above for commutator pairs.
217
<title><literal>RESTRICT</></title>
220
The <literal>RESTRICT</> clause, if provided, names a restriction selectivity
221
estimation function for the operator. (Note that this is a function
222
name, not an operator name.) <literal>RESTRICT</> clauses only make sense for
223
binary operators that return <type>boolean</>. The idea behind a restriction
224
selectivity estimator is to guess what fraction of the rows in a
225
table will satisfy a <literal>WHERE</literal>-clause condition of the form:
229
for the current operator and a particular constant value.
230
This assists the optimizer by
231
giving it some idea of how many rows will be eliminated by <literal>WHERE</>
232
clauses that have this form. (What happens if the constant is on
233
the left, you might be wondering? Well, that's one of the things that
234
<literal>COMMUTATOR</> is for...)
238
Writing new restriction selectivity estimation functions is far beyond
239
the scope of this chapter, but fortunately you can usually just use
240
one of the system's standard estimators for many of your own operators.
241
These are the standard restriction estimators:
243
<member><function>eqsel</> for <literal>=</></member>
244
<member><function>neqsel</> for <literal><></></member>
245
<member><function>scalarltsel</> for <literal><</> or <literal><=</></member>
246
<member><function>scalargtsel</> for <literal>></> or <literal>>=</></member>
248
It might seem a little odd that these are the categories, but they
249
make sense if you think about it. <literal>=</> will typically accept only
250
a small fraction of the rows in a table; <literal><></> will typically reject
251
only a small fraction. <literal><</> will accept a fraction that depends on
252
where the given constant falls in the range of values for that table
253
column (which, it just so happens, is information collected by
254
<command>ANALYZE</command> and made available to the selectivity estimator).
255
<literal><=</> will accept a slightly larger fraction than <literal><</> for the same
256
comparison constant, but they're close enough to not be worth
257
distinguishing, especially since we're not likely to do better than a
258
rough guess anyhow. Similar remarks apply to <literal>></> and <literal>>=</>.
262
You can frequently get away with using either <function>eqsel</function> or <function>neqsel</function> for
263
operators that have very high or very low selectivity, even if they
264
aren't really equality or inequality. For example, the
265
approximate-equality geometric operators use <function>eqsel</function> on the assumption that
266
they'll usually only match a small fraction of the entries in a table.
270
You can use <function>scalarltsel</> and <function>scalargtsel</> for comparisons on data types that
271
have some sensible means of being converted into numeric scalars for
272
range comparisons. If possible, add the data type to those understood
273
by the function <function>convert_to_scalar()</function> in <filename>src/backend/utils/adt/selfuncs.c</filename>.
274
(Eventually, this function should be replaced by per-data-type functions
275
identified through a column of the <classname>pg_type</> system catalog; but that hasn't happened
276
yet.) If you do not do this, things will still work, but the optimizer's
277
estimates won't be as good as they could be.
281
There are additional selectivity estimation functions designed for geometric
282
operators in <filename>src/backend/utils/adt/geo_selfuncs.c</filename>: <function>areasel</function>, <function>positionsel</function>,
283
and <function>contsel</function>. At this writing these are just stubs, but you might want
284
to use them (or even better, improve them) anyway.
289
<title><literal>JOIN</></title>
292
The <literal>JOIN</> clause, if provided, names a join selectivity
293
estimation function for the operator. (Note that this is a function
294
name, not an operator name.) <literal>JOIN</> clauses only make sense for
295
binary operators that return <type>boolean</type>. The idea behind a join
296
selectivity estimator is to guess what fraction of the rows in a
297
pair of tables will satisfy a <literal>WHERE</>-clause condition of the form:
299
table1.column1 OP table2.column2
301
for the current operator. As with the <literal>RESTRICT</literal> clause, this helps
302
the optimizer very substantially by letting it figure out which
303
of several possible join sequences is likely to take the least work.
307
As before, this chapter will make no attempt to explain how to write
308
a join selectivity estimator function, but will just suggest that
309
you use one of the standard estimators if one is applicable:
311
<member><function>eqjoinsel</> for <literal>=</></member>
312
<member><function>neqjoinsel</> for <literal><></></member>
313
<member><function>scalarltjoinsel</> for <literal><</> or <literal><=</></member>
314
<member><function>scalargtjoinsel</> for <literal>></> or <literal>>=</></member>
315
<member><function>areajoinsel</> for 2D area-based comparisons</member>
316
<member><function>positionjoinsel</> for 2D position-based comparisons</member>
317
<member><function>contjoinsel</> for 2D containment-based comparisons</member>
323
<title><literal>HASHES</></title>
326
The <literal>HASHES</literal> clause, if present, tells the system that
327
it is permissible to use the hash join method for a join based on this
328
operator. <literal>HASHES</> only makes sense for a binary operator that
329
returns <literal>boolean</>, and in practice the operator must represent
330
equality for some data type or pair of data types.
334
The assumption underlying hash join is that the join operator can
335
only return true for pairs of left and right values that hash to the
336
same hash code. If two values get put in different hash buckets, the
337
join will never compare them at all, implicitly assuming that the
338
result of the join operator must be false. So it never makes sense
339
to specify <literal>HASHES</literal> for operators that do not represent
340
some form of equality. In most cases it is only practical to support
341
hashing for operators that take the same data type on both sides.
342
However, sometimes it is possible to design compatible hash functions
343
for two or more data types; that is, functions that will generate the
344
same hash codes for <quote>equal</> values, even though the values
345
have different representations. For example, it's fairly simple
346
to arrange this property when hashing integers of different widths.
350
To be marked <literal>HASHES</literal>, the join operator must appear
351
in a hash index operator family. This is not enforced when you create
352
the operator, since of course the referencing operator family couldn't
353
exist yet. But attempts to use the operator in hash joins will fail
354
at run time if no such operator family exists. The system needs the
355
operator family to find the data-type-specific hash function(s) for the
356
operator's input data type(s). Of course, you must also create suitable
357
hash functions before you can create the operator family.
361
Care should be exercised when preparing a hash function, because there
362
are machine-dependent ways in which it might fail to do the right thing.
363
For example, if your data type is a structure in which there might be
364
uninteresting pad bits, you cannot simply pass the whole structure to
365
<function>hash_any</>. (Unless you write your other operators and
366
functions to ensure that the unused bits are always zero, which is the
367
recommended strategy.)
368
Another example is that on machines that meet the <acronym>IEEE</>
369
floating-point standard, negative zero and positive zero are different
370
values (different bit patterns) but they are defined to compare equal.
371
If a float value might contain negative zero then extra steps are needed
372
to ensure it generates the same hash value as positive zero.
376
A hash-joinable operator must have a commutator (itself if the two
377
operand data types are the same, or a related equality operator
378
if they are different) that appears in the same operator family.
379
If this is not the case, planner errors might occur when the operator
380
is used. Also, it is a good idea (but not strictly required) for
381
a hash operator family that supports multiple data types to provide
382
equality operators for every combination of the data types; this
383
allows better optimization.
388
The function underlying a hash-joinable operator must be marked
389
immutable or stable. If it is volatile, the system will never
390
attempt to use the operator for a hash join.
396
If a hash-joinable operator has an underlying function that is marked
398
function must also be complete: that is, it should return true or
399
false, never null, for any two nonnull inputs. If this rule is
400
not followed, hash-optimization of <literal>IN</> operations might
401
generate wrong results. (Specifically, <literal>IN</> might return
402
false where the correct answer according to the standard would be null;
403
or it might yield an error complaining that it wasn't prepared for a
411
<title><literal>MERGES</></title>
414
The <literal>MERGES</literal> clause, if present, tells the system that
415
it is permissible to use the merge-join method for a join based on this
416
operator. <literal>MERGES</> only makes sense for a binary operator that
417
returns <literal>boolean</>, and in practice the operator must represent
418
equality for some data type or pair of data types.
422
Merge join is based on the idea of sorting the left- and right-hand tables
423
into order and then scanning them in parallel. So, both data types must
424
be capable of being fully ordered, and the join operator must be one
425
that can only succeed for pairs of values that fall at the
427
in the sort order. In practice this means that the join operator must
428
behave like equality. But it is possible to merge-join two
429
distinct data types so long as they are logically compatible. For
430
example, the <type>smallint</type>-versus-<type>integer</type>
431
equality operator is merge-joinable.
432
We only need sorting operators that will bring both data types into a
433
logically compatible sequence.
437
To be marked <literal>MERGES</literal>, the join operator must appear
438
as an equality member of a <literal>btree</> index operator family.
439
This is not enforced when you create
440
the operator, since of course the referencing operator family couldn't
441
exist yet. But the operator will not actually be used for merge joins
442
unless a matching operator family can be found. The
443
<literal>MERGES</literal> flag thus acts as a hint to the planner that
444
it's worth looking for a matching operator family.
448
A merge-joinable operator must have a commutator (itself if the two
449
operand data types are the same, or a related equality operator
450
if they are different) that appears in the same operator family.
451
If this is not the case, planner errors might occur when the operator
452
is used. Also, it is a good idea (but not strictly required) for
453
a <literal>btree</> operator family that supports multiple data types to provide
454
equality operators for every combination of the data types; this
455
allows better optimization.
460
The function underlying a merge-joinable operator must be marked
461
immutable or stable. If it is volatile, the system will never
462
attempt to use the operator for a merge join.
added
removed
doc/src/sgml/xoper.sgml
