1ALTER OPERATOR FAMILY(7)         SQL Commands         ALTER OPERATOR FAMILY(7)
2
3
4

NAME

6       ALTER OPERATOR FAMILY - change the definition of an operator family
7
8

SYNOPSIS

10       ALTER OPERATOR FAMILY name USING index_method ADD
11         {  OPERATOR strategy_number operator_name ( op_type, op_type )
12          | FUNCTION support_number [ ( op_type [ , op_type ] ) ] funcname ( argument_type [, ...] )
13         } [, ... ]
14       ALTER OPERATOR FAMILY name USING index_method DROP
15         {  OPERATOR strategy_number ( op_type [ , op_type ] )
16          | FUNCTION support_number ( op_type [ , op_type ] )
17         } [, ... ]
18       ALTER OPERATOR FAMILY name USING index_method RENAME TO newname
19       ALTER OPERATOR FAMILY name USING index_method OWNER TO newowner
20
21

DESCRIPTION

23       ALTER OPERATOR FAMILY changes the definition of an operator family. You
24       can add operators and support functions to the family, remove them from
25       the family, or change the family's name or owner.
26
27       When  operators  and support functions are added to a family with ALTER
28       OPERATOR FAMILY, they are not  part  of  any  specific  operator  class
29       within the family, but are just ``loose'' within the family. This indi‐
30       cates that these operators and functions are compatible with  the  fam‐
31       ily's  semantics,  but  are not required for correct functioning of any
32       specific index. (Operators and functions that are so required should be
33       declared  as  part  of  an operator class, instead; see CREATE OPERATOR
34       CLASS [create_operator_class(7)].)  PostgreSQL will allow loose members
35       of  a  family to be dropped from the family at any time, but members of
36       an operator class cannot be dropped without dropping  the  whole  class
37       and  any indexes that depend on it.  Typically, single-data-type opera‐
38       tors and functions are part of operator classes because they are needed
39       to  support  an index on that specific data type, while cross-data-type
40       operators and functions are made loose members of the family.
41
42       You must be a superuser to use ALTER OPERATOR FAMILY.   (This  restric‐
43       tion is made because an erroneous operator family definition could con‐
44       fuse or even crash the server.)
45
46       ALTER OPERATOR FAMILY does not presently  check  whether  the  operator
47       family  definition includes all the operators and functions required by
48       the index method, nor whether the operators and functions form a  self-
49       consistent set. It is the user's responsibility to define a valid oper‐
50       ator family.
51
52       Refer to in the documentation for further information.
53

PARAMETERS

55       name   The name (optionally schema-qualified) of an  existing  operator
56              family.
57
58       index_method
59              The name of the index method this operator family is for.
60
61       strategy_number
62              The  index  method's  strategy number for an operator associated
63              with the operator family.
64
65       operator_name
66              The name (optionally schema-qualified) of an operator associated
67              with the operator family.
68
69       op_type
70              In an OPERATOR clause, the operand data type(s) of the operator,
71              or NONE to signify a left-unary or right-unary operator.  Unlike
72              the comparable syntax in CREATE OPERATOR CLASS, the operand data
73              types must always be specified.
74
75              In an ADD FUNCTION clause, the operand data type(s) the function
76              is intended to support, if different from the input data type(s)
77              of the function. For B-tree and hash indexes it is not necessary
78              to  specify  op_type since the function's input data type(s) are
79              always the correct ones to use. For GIN and GiST indexes  it  is
80              necessary  to  specify the input data type the function is to be
81              used with.
82
83              In a DROP FUNCTION clause, the operand data type(s) the function
84              is intended to support must be specified.
85
86       support_number
87              The index method's support procedure number for a function asso‐
88              ciated with the operator family.
89
90       funcname
91              The name (optionally schema-qualified) of a function that is  an
92              index method support procedure for the operator family.
93
94       argument_types
95              The parameter data type(s) of the function.
96
97       newname
98              The new name of the operator family.
99
100       newowner
101              The new owner of the operator family.
102
103       The OPERATOR and FUNCTION clauses can appear in any order.
104

NOTES

106       Notice that the DROP syntax only specifies the ``slot'' in the operator
107       family, by strategy or support number and input data type(s). The  name
108       of  the operator or function occupying the slot is not mentioned. Also,
109       for DROP FUNCTION the type(s) to specify are the input data type(s) the
110       function  is  intended  to support; for GIN and GiST indexes this might
111       have nothing to do with the actual input argument types  of  the  func‐
112       tion.
113
114       Because  the index machinery does not check access permissions on func‐
115       tions before using them, including a function or operator in an  opera‐
116       tor  family  is tantamount to granting public execute permission on it.
117       This is usually not an issue for the sorts of functions that are useful
118       in an operator family.
119
120       The operators should not be defined by SQL functions. A SQL function is
121       likely to be inlined into the calling query,  which  will  prevent  the
122       optimizer from recognizing that the query matches an index.
123
124       Before  PostgreSQL  8.4,  the  OPERATOR  clause could include a RECHECK
125       option. This is no longer supported because whether an  index  operator
126       is ``lossy'' is now determined on-the-fly at runtime. This allows effi‐
127       cient handling of cases where an operator might or might not be lossy.
128

EXAMPLES

130       The following example command adds cross-data-type operators  and  sup‐
131       port functions to an operator family that already contains B-tree oper‐
132       ator classes for data types int4 and int2.
133
134       ALTER OPERATOR FAMILY integer_ops USING btree ADD
135
136         -- int4 vs int2
137         OPERATOR 1 < (int4, int2) ,
138         OPERATOR 2 <= (int4, int2) ,
139         OPERATOR 3 = (int4, int2) ,
140         OPERATOR 4 >= (int4, int2) ,
141         OPERATOR 5 > (int4, int2) ,
142         FUNCTION 1 btint42cmp(int4, int2) ,
143
144         -- int2 vs int4
145         OPERATOR 1 < (int2, int4) ,
146         OPERATOR 2 <= (int2, int4) ,
147         OPERATOR 3 = (int2, int4) ,
148         OPERATOR 4 >= (int2, int4) ,
149         OPERATOR 5 > (int2, int4) ,
150         FUNCTION 1 btint24cmp(int2, int4) ;
151
152
153       To remove these entries again:
154
155       ALTER OPERATOR FAMILY integer_ops USING btree DROP
156
157         -- int4 vs int2
158         OPERATOR 1 (int4, int2) ,
159         OPERATOR 2 (int4, int2) ,
160         OPERATOR 3 (int4, int2) ,
161         OPERATOR 4 (int4, int2) ,
162         OPERATOR 5 (int4, int2) ,
163         FUNCTION 1 (int4, int2) ,
164
165         -- int2 vs int4
166         OPERATOR 1 (int2, int4) ,
167         OPERATOR 2 (int2, int4) ,
168         OPERATOR 3 (int2, int4) ,
169         OPERATOR 4 (int2, int4) ,
170         OPERATOR 5 (int2, int4) ,
171         FUNCTION 1 (int2, int4) ;
172
173

COMPATIBILITY

175       There is no ALTER OPERATOR FAMILY statement in the SQL standard.
176

SEE ALSO

178       CREATE OPERATOR FAMILY [create_operator_family(7)], DROP OPERATOR  FAM‐
179       ILY  [drop_operator_family(7)],  CREATE  OPERATOR  CLASS [create_opera‐
180       tor_class(7)], ALTER  OPERATOR  CLASS  [alter_operator_class(7)],  DROP
181       OPERATOR CLASS [drop_operator_class(7)]
182
183
184
185SQL - Language Statements         2014-02-17          ALTER OPERATOR FAMILY(7)
Impressum