create_operator man page on BSDi

Man page or keyword search:  
man Server   6284 pages
apropos Keyword Search (all sections)
Output format
BSDi logo
[printable version]

CREATE OPERATOR()		 SQL Commands		     CREATE OPERATOR()

NAME
       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  operator,  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 opera‐
	      tor.

       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 created.

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 comment.

       · 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-compliant  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 opera‐
       tors,  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 using CREATE
       FUNCTION and must be defined to accept the 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 exam‐
       ple, 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 somewhat.

       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  sim‐
       plify

       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 cre‐
       ated operator as its commutator. This applies to the negator, as	 well.
       This  is to allow the definition of two operators that are the commuta‐
       tors or the negators of	each  other.  The  first  operator  should  be
       defined without a commutator or negator (as appropriate). When the sec‐
       ond 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 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 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 rep‐
       resent 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 respectively.

       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.

       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 func‐
       tion  res_proc  must  be	 a  registered 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 optimizer
       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	multiplied  by	the  cardinalities  of	the two tables
       involved to compute the expected result size.

       The difference between the function

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

       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. How‐
       ever, 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-equality,  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 CREATE OPERATOR
       statement in SQL92.

SQL - Language Statements	 29 March 2001		     CREATE OPERATOR()
[top]

List of man pages available for BSDi

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net