head	1.15;
access;
symbols;
locks; strict;
comment	@# @;


1.15
date	94.03.12.10.48.32;	author aoki;	state Exp;
branches;
next	1.14;

1.14
date	94.03.11.07.07.59;	author aoki;	state Exp;
branches;
next	1.13;

1.13
date	94.02.04.22.26.51;	author aoki;	state Exp;
branches;
next	1.12;

1.12
date	93.05.26.20.11.39;	author avi;	state Exp;
branches;
next	1.11;

1.11
date	93.01.26.02.43.13;	author aoki;	state Exp;
branches;
next	1.10;

1.10
date	92.07.14.05.54.17;	author ptong;	state Exp;
branches;
next	1.9;

1.9
date	92.07.13.03.45.59;	author ptong;	state Exp;
branches;
next	1.8;

1.8
date	91.12.02.04.02.08;	author kemnitz;	state Exp;
branches;
next	1.7;

1.7
date	91.08.23.13.21.08;	author kemnitz;	state Exp;
branches;
next	1.6;

1.6
date	91.02.25.15.17.32;	author mike;	state Exp;
branches;
next	1.5;

1.5
date	91.01.28.23.04.58;	author mao;	state Exp;
branches;
next	1.4;

1.4
date	90.08.02.16.16.33;	author choi;	state Exp;
branches;
next	1.3;

1.3
date	90.07.21.18.17.23;	author kemnitz;	state Exp;
branches;
next	1.2;

1.2
date	90.07.21.18.14.18;	author kemnitz;	state Exp;
branches;
next	1.1;

1.1
date	90.07.18.16.22.03;	author mao;	state Exp;
branches;
next	;


desc
@man page for the defineoperator postquel command
@


1.15
log
@formatting
@
text
@.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" $Header: /usr/local/devel/postgres/src/ref/postquel/RCS/define_operator.cmdsrc,v 1.14 1994/03/11 07:07:59 aoki Exp aoki $
.TH "DEFINE OPERATOR" COMMANDS 03/12/94
.XA 2 "Define Operator"
.SH NAME
define operator \(em define a new user operator
.SH SYNOPSIS
.(l M
\fBdefine operator\fR operator_name
	\fB(\fR[ \fBarg1\fR \fB=\fR type-1 ]
	 [ \fB,\fR \fBarg2\fR \fB=\fR type-2 ]
	 , \fBprocedure =\fR func_name
	 [\fB, precedence =\fR number ]
	 [\fB, associativity =\fR \fB(left | right | none | any)\fR ]
	 [\fB, commutator =\fR com_op ]
	 [\fB, negator =\fR neg_op ]
	 [\fB, restrict =\fR res_proc ]
	 [\fB, hashes\fR]
	 [\fB, join =\fR join_proc ]
	 [\fB, sort =\fR sor_op1 {\fB,\fR sor_op2 } ]
	\fB)\fR
.\" \fB"arg is ("
.\" type [
.\" \fB,
.\" type ]
.\" \fB)
.)l
.SH DESCRIPTION
This command defines a new user operator,
.IR "operator_name" .
The user who defines an operator becomes its owner.
.PP
The
.IR "operator_name"
is a sequence of up to sixteen punctuation characters.  The following
characters are valid for single-character operator names:
.(C
~ ! @@ # % ^ & ` ?
.)C
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:
.(C
| $ : + - * / < > =
.)C
.PP
At least one of
.IR arg1
and
.IR arg2
must be defined.  For binary operators, both should be defined. For
right unary operators, only
.IR arg1
should be defined, while for left unary operators only
.IR arg2
should be defined.
.PP
The name of the operator,
.IR operator_name ,
can be composed of symbols only.  Also, the
.IR func_name
procedure must have been previously defined using
.IR "define function" (commands)
and must have one or two arguments.  The types of the arguments for
the operator and the type of the answer are as defined by the
function.
.BR Precedence
refers to the order that multiple instances of the same operator are
evaluated.
.\" what does this number mean and what is its range?
The next several fields are primarily for the use of the query
optimizer.
.PP
The
.BR associativity
value is used to indicate how an expression containing this operator
should be evaluated when precedence and explicit grouping are
insufficient to produce a complete order of evaluation.
.BR Left
and
.BR right
indicate that expressions containing the operator are to be evaluated
from left to right or from right to left, respectively.
.BR None
means that it is an error for this operator to be used without
explicit grouping when there is ambiguity.  And
.BR any ,
the default, indicates that the optimizer may choose to evaluate an
expression which contains this operator arbitrarily.
.PP
.\" that multiple instances of the 
.\" operator must be be evaluated
.\" For example, consider the area-intersection operator,
.\" .q A,
.\" and the following expression:
.\" .(l
.\" MYBOXES2.description A \*(lq0,0,1,1\*(rq A MYBOXES.description
.\" .)l
.\" .in .5i
.\" The associativity flag indicates that
.\" .(l
.\" (MYBOXES2.description A \*(lq0,0,1,1\*(rq) A MYBOXES.description
.\" .)l
.\" .in .5i
.\" is the same as
.\" .(l
.\" MYBOXES2.description A (\*(lq0,0,1,1\*(rq A MYBOXES.description).
.\" .)l
The commutator operator is present so that \*(PG 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:
.(C
"0,0,1,1"::box >>> MYBOXES.description
.)C
to
.(C
MYBOXES.description <<< "0,0,1,1"::box
.)C
This allows the execution code to always use the latter representation
and simplifies the query optimizer somewhat.
.PP
The negator operator allows the query optimizer to convert
.(C
not MYBOXES.description === "0,0,1,1"::box
.)C
to
.(C
MYBOXES.description !== "0,0,1,1"::box
.)C
If a commutator operator name is supplied, \*(PG 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.
.PP
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.
.PP
The next two specifications are present to support the query optimizer
in performing joins.  \*(PG 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, \*(PG 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:
.(C
MYBOXES.description === MYBOXES2.description
.)C
but not for a clause of the form:
.(C
MYBOXES.description <<< MYBOXES2.description.
.)C
The
.BR hashes
flag gives the needed information to the query optimizer concerning
whether a hash join strategy is usable for the operator in question.
.PP
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:
.(C
MYBOXES.description <<< MYBOXES2.description
.)C
If other join strategies are found to be practical, \*(PG 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.
.PP
The last two pieces of the specification are present so the query
optimizer can estimate result sizes.  If a clause of the form:
.(C
MYBOXES.description <<< "0,0,1,1"::box
.)C
is present in the qualification, then \*(PG 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
.IR "define function" (commands))
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
.(C
"0,0,1,1"
.)C
and multiplies the result by the relation size to get the desired
expected number of instances.
.PP
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.
.PP
The difference between the function
.(C
my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
.)C
and the operator
.(C
MYBOXES.description === "0,0,1,1"::box
.)C
is that \*(PG 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.
.SH EXAMPLE
.(C
/*
 * The following command defines a new operator,
 * area-equality, for the BOX data type.
 */
define operator === (
	arg1 = box,
	arg2 = box,
	procedure = area_equal_procedure,
	precedence = 30,
	associativity = left,
	commutator = ===,
	negator = !==,
	restrict = area_restriction_procedure,
	hashes,
	join = area-join-procedure,
	sort = <<<, <<<)
.\"	arg is (box, box)
.)C
.SH "SEE ALSO"
define function(commands),
remove operator(commands).
.SH BUGS
Operator names cannot be composed of alphabetic characters in Version
\*(PV.
.PP
Operator precedence is not implemented in Version \*(PV.
.PP
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.
@


1.14
log
@valid operator characters
@
text
@d3 2
a4 2
.\" $Header: /usr/local/devel/postgres/src/ref/postquel/RCS/define_operator.cmdsrc,v 1.13 1994/02/04 22:26:51 aoki Exp aoki $
.TH "DEFINE OPERATOR" COMMANDS 03/10/94
d64 1
a64 1
.BR "define function"
d190 1
a190 1
.BR "define function" )
@


1.13
log
@warning about defining an operator before its commutator
has been defined (actually, an additional warning)
also, avi put associativity in
@
text
@d3 2
a4 2
.\" $Header: /usr/local/devel/postgres/src/ref/postquel/RCS/define_operator.cmdsrc,v 1.12 1993/05/26 20:11:39 avi Exp aoki $
.TH "DEFINE OPERATOR" COMMANDS 01/23/93
d34 14
@


1.12
log
@made arg1 optional, but stressed that at least one of arg1 and arg2 must
be defined.
@
text
@d3 1
a3 1
.\" $Header: /private/src/postgres/src/ref/postquel/RCS/define_operator.cmdsrc,v 1.11 1993/01/26 02:43:13 aoki Exp avi $
d232 6
a237 2
Operator precedence and associativity are not implemented in Version
\*(PV.
@


1.11
log
@-man version
@
text
@d3 1
a3 1
.\" $Header: /home2/aoki/ref/postquel/RCS/defineoperator,v 1.10 1992/07/14 05:54:17 ptong Exp aoki $
d11 1
a11 1
	\fB(\fR \fBarg1\fR \fB=\fR type-1
d33 11
@


1.10
log
@reformated
@
text
@d1 1
d3 2
a4 2
.\" $Header: /home/postgres/mer/refs/postquel/RCS/defineoperator,v 1.9 1992/07/13 03:45:59 ptong Exp ptong $
.SP "DEFINE OPERATOR" COMMANDS 6/14/90
d6 4
a9 6
.uh NAME
.lp
define operator \*- define a new user operator
.uh SYNOPSIS
.lp
.(l
d29 1
a29 2
.uh DESCRIPTION
.lp
d31 1
a31 1
.i "operator_name" .
d33 1
a33 1
.lp
d35 11
a45 15
.i operator_name ,
can be composed of symbols only. 
Also, the
.i func_name
procedure
must have been previously defined using 
.b "define function"
and must have one
or two arguments.
The types of the arguments for the operator 
and the type of the
answer are as defined by the function.
.b Precedence
refers to the order that multiple instances of the same
operator are evaluated.
d47 3
a49 2
The next several fields are primarily for the use of the query optimizer.
.lp
d51 5
a55 6
.b associativity
value is used to indicate how an expression containing
this operator should be evaluated when precedence
and explicit grouping are insufficient to produce
a complete order of evaluation.
.b Left
d57 10
a66 14
.b right
indicate that expressions containing
the operator are to be evaluated from left to right or
from right to left, respectively.
.b None
means that it is an error for
this operator to be used without
explicit grouping when there is ambiguity.
And
.b any ,
the default,
indicates that the optimizer may choose to evaluate
an expression which contains this operator arbitrarily.
.lp
d85 7
a91 8
The commutator operator is present so that \*(PP 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:
.(l
.ft C
d93 1
a93 3
.ft
.)l
.in .5i
d95 1
a95 2
.(l
.ft C
d97 1
a97 3
.ft
.)l
.in .5i
d100 1
a100 1
.sp
d102 1
a102 2
.(l
.ft C
d104 1
a104 3
.ft
.)l
.in .5i
d106 1
a106 2
.(l
.ft C
d108 7
a114 12
.ft
.)l
.in .5i
.sp
If a commutator operator name is supplied,
\*(PP 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.
.sp
d116 5
a120 7
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.
.sp
d122 8
a129 11
in performing joins.
\*(PP 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,
\*(PP 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:
.(l
.ft C
d131 1
a131 3
.ft
.)l
.in .5i
d133 1
a133 2
.(l
.ft C
d135 1
a135 3
.ft
.)l
.in .5i
d137 10
a146 15
.i hashes
flag gives the needed information to the query optimizer
concerning whether a hash join strategy is usable for the operator
in question.  
.sp
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: 
.(l
.ft C
d148 6
a153 9
.ft
.)l
If other join strategies are found to be practical,
\*(PP 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
d155 2
a156 2
.lp
The last two pieces of the specification are present so the query 
d158 1
a158 2
.(l
.ft C
d160 10
a169 14
.ft
.)l
.in .5i
is present in the qualification, 
then \*(PP 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
.b "define function"
)
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
.ft C
d171 8
a178 8
.ft
and multiplies the result by the relation size to get the desired expected
number of instances.
.sp
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
d180 1
a180 1
.sp
d182 1
a182 2
.(l
.ft C
d184 1
a184 3
.ft
.)l
.in .5i
d186 1
a186 2
.(l
.ft C
d188 12
a199 17
.ft
.)l
.in .5i
is that \*(PP 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.
.sp
.(b
.uh EXAMPLE
.lp
.nf
.ft C
/* The following command defines a new operator, */
/* area-equality, for the BOX data type.         */

d213 10
a222 11
.ft
.)b
.uh "SEE ALSO"
.lp
remove operator(commands),
define function(commands).
.uh BUGS
.lp
Operator names cannot be composed of alphabetic characters in Version \*(PV.
.lp
Operator precedence and associativity are not implemented in Version \*(PV.
@


1.9
log
@Fixed up formating
@
text
@d2 1
a2 1
.\" $Header: /home/postgres/mer/refs/postquel/RCS/defineoperator,v 1.8 1991/12/02 04:02:08 kemnitz Exp ptong $
d254 2
a255 2
/* The following command defines a new operator, area-equality,
   for the BOX data type. */
@


1.8
log
@*** empty log message ***
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.7 91/08/23 13:21:08 kemnitz Exp Locker: kemnitz $
d10 15
a24 37
.b "define operator"
operator_name
.b "( arg1 ="
type-1
.b "[, arg2 ="
type-2 ]
.b " procedure ="
func_name
[
.b ", precedence ="
number ] [
.b ", associativity ="
(
.b left
|
.b right
|
.b none
|
.b any
) ] [
.b ", commutator ="
com_op ] [
.b ", negator ="
neg_op ] [
.b ", restrict ="
res_proc ] [
.b ", hashes"
] [
.b ", join ="
join_proc ] [
.b ", sort ="
sor_op1 {
.b ,
sor_op2 } ]
.b )
.\" .b "arg is ("
d26 1
a26 1
.\" .b ,
d28 2
a29 1
.\" .b )
d102 3
a104 1
\*(lq0,0,1,1\*(rq::box >>> MYBOXES.description
d109 3
a111 1
MYBOXES.description <<< \*(lq0,0,1,1\*(rq::box
d119 3
a121 1
not MYBOXES.description === \*(lq0,0,1,1\*(rq::box
d126 3
a128 1
MYBOXES.description !== \*(lq0,0,1,1\*(rq::box
d133 1
a133 1
POSTGRES searches for it in the catalog.
d159 1
d161 1
d166 1
d168 1
d186 1
d188 1
d202 3
a204 1
MYBOXES.description <<< \*(lq0,0,1,1\*(rq::box
d216 6
a221 3
simply calls this function, passing the parameter \*(lq0,0,1,1\*(rq and
multiplies the result by the relation size to get the desired expected number
of instances.
d231 3
a233 1
my_procedure_1 (MYBOXES.description, \*(lq0,0,1,1\*(rq::box)
d238 3
a240 1
MYBOXES.description === \*(lq0,0,1,1\*(rq::box
d253 1
d270 1
d274 2
a275 2
remove operator(commands)
define function(commands)
d278 3
a280 3
Operator names cannot be composed of alphabetic
characters in Version 3.0.
Operator precedence and associativity are not implemented in Version 3.0.
@


1.7
log
@2.1 -> 3.0
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.6 91/02/25 15:17:32 mike Exp Locker: kemnitz $
d64 1
a64 1
.b "define C function"
d217 1
a217 1
.b "define C function"
d271 1
a271 1
define C function(commands)
@


1.6
log
@objectified
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.5 91/01/28 23:04:58 mao Exp Locker: mike $
d275 2
a276 2
characters in Version 2.1.
Operator precedence and associativity are not implemented in Version 2.1.
@


1.5
log
@document handling of operators that refer to one another as commutators
or negators.
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.4 90/08/02 16:16:33 choi Exp Locker: mao $
d62 1
a62 1
function
d190 1
a190 1
sort the two operand relations.
d214 1
a214 1
estimate the fraction of the tuples in MYBOXES
d223 1
a223 1
of tuples.
d225 1
a225 1
Similarly, when the operands of the operator both contain tuple variables,
d229 1
a229 1
relations involved to compute the desired expected result size.
d275 2
a276 2
characters in Version 2.
Operator precedence and associativity are not implemented in Version 2.
@


1.4
log
@define operator takes "arg1 = type-1, [arg2 = type-2]" as arguments
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.3 90/07/21 18:17:23 kemnitz Exp Locker: choi $
d144 16
@


1.3
log
@Fixed some junk.
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.2 90/07/21 18:14:18 kemnitz Exp Locker: kemnitz $
d12 5
a16 1
.b "( procedure ="
d239 2
@


1.2
log
@Changed syntax around somewhat to agree with implementation.
@
text
@d2 1
a2 1
.\" $Header: RCS/defineoperator,v 1.1 90/07/18 16:22:03 mao Exp Locker: kemnitz $
d60 1
a60 1
.b define C function
d197 1
a197 1
.b define C function
d248 2
a249 1
remove operator(commands).
@


1.1
log
@Initial revision
@
text
@d2 1
a2 1
.\" $Header: defineoperator,v 1.9 89/03/30 10:07:36 wensel Exp $
d13 1
a13 1
proc_name
d55 1
a55 8
can be composed of alphabetic characters
or symbols, but not a combination
of the two (i.e.,
.b A
is valid,
but
.b ^A^
is not).
d57 5
a61 3
.i proc_name
procedure
must have been previously defined and must have one
d65 1
a65 1
answer are as defined by the procedure.
d114 4
a117 3
For example, the operator area-less-than, ALT,
would have a commutator operator, area-greater-than, AGT.
Hence, the query optimizer could freely convert:
d119 1
a119 1
\*(lq0,0,1,1\*(rq::box ALT MYBOXES.description
d124 1
a124 1
MYBOXES.description AGT \*(lq0,0,1,1\*(rq::box
d132 1
a132 1
not MYBOXES.description AE \*(lq0,0,1,1\*(rq::box
d137 1
a137 1
MYBOXES.description NAE \*(lq0,0,1,1\*(rq::box
d152 1
a152 1
MYBOXES.description AE MYBOXES2.description
d157 1
a157 1
MYBOXES.description ALT MYBOXES2.description.
d171 2
a172 2
For the AE clause above, the optimizer must
sort both relations using the operator, ALT.
d175 1
a175 1
MYBOXES.description ALT MYBOXES2.description
d189 1
a189 1
MYBOXES.description ALT \*(lq0,0,1,1\*(rq::box
d196 4
a199 1
be a registered function which accepts one argument of the correct
d213 1
a213 1
my-procedure-1 (MYBOXES.description, \*(lq0,0,1,1\*(rq::box)
d218 1
a218 1
MYBOXES.description AE \*(lq0,0,1,1\*(rq::box
d234 2
a235 2
define operator AE (
	procedure = area-equal-procedure,
d238 3
a240 3
	commutator = AE,
	negator = ANE,
	restrict = area-restriction-procedure,
d243 1
a243 1
	sort = ALT, ALT)
@