1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>"CREATE(OPERATOR") manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
create operator - define a new user operator
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>create operator
</B> operator_name <BR>
<tt> </tt><tt> </tt><B>( </B>[ <B>leftarg </B> <B>= </B> type-1 ] <BR>
<tt> </tt><tt> </tt> [ <B>, </B> <B>rightarg </B> <B>= </B> type-2 ] <BR>
<tt> </tt><tt> </tt>
, <B>procedure = </B> func_name <BR>
<tt> </tt><tt> </tt> [<B>, commutator = </B> com_op ] <BR>
<tt> </tt><tt> </tt> [<B>, negator = </B> neg_op
] <BR>
<tt> </tt><tt> </tt> [<B>, restrict = </B> res_proc ] <BR>
<tt> </tt><tt> </tt> [<B>, hashes </B>] <BR>
<tt> </tt><tt> </tt> [<B>, join = </B> join_proc ] <BR>
<tt> </tt><tt> </tt> [<B>, sort = </B> sor_op1 {<B>, </B> sor_op2 } ] <BR>
<tt> </tt><tt> </tt><B>) </B> <BR>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
This command defines
a new user operator, <I>operator_name</I>. The user who defines an operator becomes
its owner. <P>
The <I>operator_name</I> is a sequence of up to sixteen punctuation
characters. The following characters are valid for single-character operator
names: <P>
<CENTER>~ ! @ # % ^ & ` ? <BR>
</CENTER> <P>
If the operator name is more than one character
long, it may consist of any combination of the above characters or the
following additional characters: <P>
<CENTER>| $ : + - * / < > = <BR>
</CENTER> <P>
The operator "!="
is mapped to "<>" on input, and they are therefore equivalent. <P>
At least one
of <I>leftarg</I> and <I>rightarg</I> must be defined. For binary operators, both should
be defined. For right unary operators, only <I>arg1</I> should be defined, while
for left unary operators only <I>arg2</I> should be defined. <P>
The name of the operator,
<I>operator_name</I>, can be composed of symbols only. Also, the <I>func_name</I> procedure
must have been previously defined using <I>create <A HREF="function.l.html">function</I>(l)</A>
and must have
one or two arguments. <P>
The commutator operator is present so that Postgres
can reverse the order of the operands if it wishes. For example, the operator
area-less-than, >>>, would have a commutator operator, area-greater-than, <<<. Suppose
that an operator, area-equal, ===, exists, as well as an area not equal,
!==. Hence, the query optimizer could freely convert: <P>
<CENTER>"0,0,1,1"::box
>>> MYBOXES.description <BR>
</CENTER> <P>
to <P>
<CENTER>MYBOXES.description <<< "0,0,1,1"::box <BR>
</CENTER> <P>
This
allows the execution code to always use the latter representation and
simplifies the query optimizer somewhat. <P>
The negator operator allows the
query optimizer to convert <P>
<CENTER>NOT MYBOXES.description === "0,0,1,1"::box
<BR>
</CENTER> <P>
to <P>
<CENTER>MYBOXES.description !== "0,0,1,1"::box <BR>
</CENTER> <P>
If a commutator operator
name is supplied, Postgres searches for it in the catalog. If it is found
and it does not yet have a commutator itself, then the commutator's entry
is updated to have the current (new) operator as its commutator. This
applies to the negator, as well. <P>
This is to allow the definition of two
operators that are the commutators or the negators of each other. The
first operator should be defined without a commutator or negator (as appropriate).
When the second operator is defined, name the first as the commutator
or negator. The first will be updated as a side effect. <P>
The next two specifications
are present to support the query optimizer in performing joins. Postgres
can always evaluate a join (i.e., processing a clause with two tuple variables
separated by an operator that returns a boolean) by iterative substitution
[WONG76]. In addition, Postgres is planning on implementing a hash-join
algorithm along the lines of [SHAP86]; however, it must know whether this
strategy is applicable. For example, a hash-join algorithm is usable for
a clause of the form: <P>
<CENTER>MYBOXES.description === MYBOXES2.description <BR>
</CENTER> <P>
but not for a clause of the form: <P>
<CENTER>MYBOXES.description <<< MYBOXES2.description.
<BR>
</CENTER> <P>
The <B>hashes</B> flag gives the needed information to the query optimizer
concerning whether a hash join strategy is usable for the operator in
question. <P>
Similarly, the two sort operators indicate to the query optimizer
whether merge-sort is a usable join strategy and what operators should
be used to sort the two operand classes. For the === clause above, the
optimizer must sort both relations using the operator, <<<. On the other
hand, merge-sort is not usable with the clause: <P>
<CENTER>MYBOXES.description <<< MYBOXES2.description
<BR>
</CENTER> <P>
If other join strategies are found to be practical, Postgres will change
the optimizer and run-time system to use them and will require additional
specification when an operator is defined. Fortunately, the research community
invents new join strategies infrequently, and the added generality of
user-defined join strategies was not felt to be worth the complexity involved.
<P>
The last two pieces of the specification are present so the query optimizer
can estimate result sizes. If a clause of the form: <P>
<CENTER>MYBOXES.description
<<< "0,0,1,1"::box <BR>
</CENTER> <P>
is present in the qualification, then Postgres may
have to estimate the fraction of the instances in MYBOXES that satisfy
the clause. The function res_proc must be a registered function (meaning
it is already defined using <I>define <A HREF="function.l.html">function</I>(l)</A>
) which accepts one argument
of the correct data type and returns a floating point number. The query
optimizer simply calls this function, passing the parameter "0,0,1,1"
and multiplies the result by the relation size to get the desired expected
number of instances. <P>
Similarly, when the operands of the operator both
contain instance variables, the query optimizer must estimate the size
of the resulting join. The function join_proc will return another floating
point number which will be multiplied by the cardinalities of the two
classes involved to compute the desired expected result size. <P>
The difference
between the function <P>
<CENTER>my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
<BR>
</CENTER> <P>
and the operator <P>
<CENTER>MYBOXES.description === "0,0,1,1"::box <BR>
</CENTER> <P>
is that
Postgres attempts to optimize operators and can decide to use an index
to restrict the search space when operators are involved. However, there
is no attempt to optimize functions, and they are performed by brute force.
Moreover, functions can have any number of arguments while operators
are restricted to one or two.
<H2><A NAME="sect3" HREF="#toc3">EXAMPLE </A></H2>
-- <BR>
--The following command defines
a new operator, <BR>
--area-equality, for the BOX data type. <BR>
-- <BR>
create operator
=== ( <BR>
<tt> </tt><tt> </tt>leftarg = box, <BR>
<tt> </tt><tt> </tt>rightarg = box, <BR>
<tt> </tt><tt> </tt>procedure = area_equal_procedure,
<BR>
<tt> </tt><tt> </tt>commutator = ===, <BR>
<tt> </tt><tt> </tt>negator = !==, <BR>
<tt> </tt><tt> </tt>restrict = area_restriction_procedure,
<BR>
<tt> </tt><tt> </tt>hashes, <BR>
<tt> </tt><tt> </tt>join = area-join-procedure, <BR>
<tt> </tt><tt> </tt>sort = <<<, <<<) <BR>
<H2><A NAME="sect4" HREF="#toc4">SEE ALSO </A></H2>
create <A HREF="function.l.html">function(l)</A>
,
drop <A HREF="operator.l.html">operator(l)</A>
.
<H2><A NAME="sect5" HREF="#toc5">BUGS </A></H2>
Operator names cannot be composed of alphabetic
characters in Postgres. <P>
If an operator is defined before its commuting
operator has been defined (a case specifically warned against above),
a dummy operator with invalid fields will be placed in the system catalogs.
This may interfere with the definition of later operators. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">EXAMPLE</A></LI>
<LI><A NAME="toc4" HREF="#sect4">SEE ALSO</A></LI>
<LI><A NAME="toc5" HREF="#sect5">BUGS</A></LI>
</UL>
</BODY></HTML>
|
