CREATE OPERATOR - Defines a new user operator


SYNOPSIS

       CREATE OPERATOR name ( PROCEDURE = func_name
            [, LEFTARG = type1 ] [, RIGHTARG = type2 ]
            [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
            [, RESTRICT = res_proc ] [, JOIN = join_proc ]
            [, HASHES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] )

   INPUTS
       name   The operator to be defined. See below for allowable
              characters.

       func_name
              The function used to implement this operator.

       type1  The type of the left-hand argument of the operator,
              if  any.   This option would be omitted for a left-
              unary operator.

       type2  The type of the right-hand argument of  the  opera­
              tor,  if  any.   This option would be omitted for a
              right-unary operator.

       com_op The commutator of this operator.

       neg_op The negator of this operator.

       res_proc
              The restriction selectivity estimator function  for
              this operator.

       join_proc
              The  join  selectivity  estimator function for this
              operator.

       HASHES Indicates this operator can support a hash join.

       left_sort_op
              If this operator can  support  a  merge  join,  the
              operator that sorts the left-hand data type of this
              operator.

       right_sort_op
              If this operator can  support  a  merge  join,  the
              operator  that  sorts  the  right-hand data type of
              this operator.

   OUTPUTS
       CREATE Message returned if the  operator  is  successfully



DESCRIPTION

       CREATE  OPERATOR  defines  a new operator, name.  The user
       who defines an operator becomes its owner.

       The operator name is a sequence of up to NAMEDATALEN-1 (31
       by default) characters from the following list:

       + - * / < > = ~ ! @ # % ^ & | ` ? $

       There are a few restrictions on your choice of name:

       · "$"  cannot  be  defined as a single-character operator,
         although it can be part of  a  multi-character  operator
         name.

       · "--"  and  "/*"  cannot  appear  anywhere in an operator
         name, since they will be taken as the start  of  a  com­
         ment.

       · A  multi-character  operator  name  cannot end in "+" or
         "-", unless the name also contains at least one of these
         characters:

         ~ ! @ # % ^ & | ` ? $

         For  example,  @- is an allowed operator name, but *- is
         not.  This restriction allows Postgres to parse SQL-com­
         pliant  queries without requiring spaces between tokens.

              Note: When working with  non-SQL-standard  operator
              names,  you  will usually need to separate adjacent
              operators with  spaces  to  avoid  ambiguity.   For
              example,  if you have defined a left-unary operator
              named "@", you cannot write X*@Y; you must write X*
              @Y to ensure that Postgres reads it as two operator
              names not one.

       The operator "!=" is mapped to "<>" on input, so these two
       names are always equivalent.

       At  least one of LEFTARG and RIGHTARG must be defined. For
       binary operators, both should be defined. For right  unary
       operators,  only LEFTARG should be defined, while for left
       unary operators only RIGHTARG should be defined.

       The func_name procedure must have been previously  defined

       correct number of arguments (either one  or  two)  of  the
       indicated types.

       The  commutator  operator  should  be  identified  if  one
       exists, so that Postgres can  reverse  the  order  of  the
       operands  if  it  wishes.  For example, the operator area-
       less-than, <<<, would probably have a commutator operator,
       area-greater-than,  >>>.  Hence, the query optimizer could
       freely convert:

       box '((0,0), (1,1))'  >>> MYBOXES.description

       to

       MYBOXES.description <<< box '((0,0), (1,1))'

       This allows the execution code to always  use  the  latter
       representation  and  simplifies  the query optimizer some­
       what.

       Similarly, if there is a negator operator then  it  should
       be identified.  Suppose that an operator, area-equal, ===,
       exists, as well as an area not equal,  !==.   The  negator
       link allows the query optimizer to simplify

       NOT MYBOXES.description === box '((0,0), (1,1))'

       to

       MYBOXES.description !== box '((0,0), (1,1))'

       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 newly created operator as its
       commutator. This applies to the negator, as well.  This is
       to allow the definition of two operators that are the com­
       mutators 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. (As of Postgres 6.5, it also
       works to just have both operators refer to each other.)

       The HASHES, SORT1, and SORT2 options are present  to  sup­
       port  the  query  optimizer in performing joins.  Postgres
       can always evaluate a join (i.e., processing a clause with

       a boolean) by iterative substitution [WONG76].   In  addi­
       tion,  Postgres  can  use  a hash-join algorithm along the
       lines of [SHAP86]; however,  it  must  know  whether  this
       strategy is applicable. The current hash-join algorithm is
       only correct for operators that represent equality  tests;
       furthermore,  equality  of the data type must mean bitwise
       equality of the representation of the type. (For  example,
       a  data  type  that contains unused bits that don't matter
       for equality tests could not be hashjoined.)   The  HASHES
       flag indicates to the query optimizer that a hash join may
       safely be used with this operator.

       Similarly, the two sort operators indicate  to  the  query
       optimizer whether merge-sort is a usable join strategy and
       which operators should be used to  sort  the  two  operand
       classes.  Sort  operators  should  only be provided for an
       equality operator, and  they  should  refer  to  less-than
       operators  for  the left and right side data types respec­
       tively.

       If other join strategies are found to be practical,  Post­
       gres  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.

       The RESTRICT and JOIN options assist the  query  optimizer
       in estimating result sizes. If a clause of the form:

       MYBOXES.description <<< box '((0,0), (1,1))'

       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 regis­
       tered function (meaning it is already defined using CREATE
       FUNCTION)  which  accepts  arguments  of  the correct data
       types and returns a floating point number. The query opti­
       mizer  simply  calls  this function, passing the parameter
       ((0,0), (1,1)) and multiplies the result by  the  relation
       size to get the expected number of instances.

       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  multi­
       plied  by  the cardinalities of the two tables involved to
       compute the expected result size.

       The difference between the function


       and the operator

       MYBOXES.description === box '((0,0), (1,1))'

       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.

   NOTES
       Refer to the chapter on operators in the PostgreSQL User's
       Guide for further information.  Refer to DROP OPERATOR  to
       delete user-defined operators from a database.


USAGE

       The  following command defines a new operator, area-equal­
       ity, for the BOX data type:

       CREATE OPERATOR === (
          LEFTARG = box,
          RIGHTARG = box,
          PROCEDURE = area_equal_procedure,
          COMMUTATOR = ===,
          NEGATOR = !==,
          RESTRICT = area_restriction_procedure,
          JOIN = area_join_procedure,
          HASHES,
          SORT1 = <<<,
          SORT2 = <<<
       );


COMPATIBILITY

   SQL92
       CREATE OPERATOR is a Postgres extension.  There is no CRE­
       ATE OPERATOR statement in SQL92.


Man(1) output converted with man2html