1ALTER OPERATOR FAMILY(7)PostgreSQL 9.2.24 DocumentationALTER OPERATOR FAMILY(7)
2
6 ALTER_OPERATOR_FAMILY - change the definition of an operator family
7
9 ALTER OPERATOR FAMILY name USING index_method ADD
10 { OPERATOR strategy_number operator_name ( op_type, op_type ) [ FOR SEARCH | FOR ORDER BY sort_family_name ]
11 | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] )
12 } [, ... ]
13 ALTER OPERATOR FAMILY name USING index_method DROP
14 { OPERATOR strategy_number ( op_type [ , op_type ] )
15 | FUNCTION support_number ( op_type [ , op_type ] )
16 } [, ... ]
17 ALTER OPERATOR FAMILY name USING index_method RENAME TO new_name
18 ALTER OPERATOR FAMILY name USING index_method OWNER TO new_owner
19 ALTER OPERATOR FAMILY name USING index_method SET SCHEMA new_schema
20
22 ALTER OPERATOR FAMILY changes the definition of an operator family. You
23 can add operators and support functions to the family, remove them from
24 the family, or change the family's name or owner.
25 When operators and support functions are added to a family with ALTER
27 OPERATOR FAMILY, they are not part of any specific operator class
28 within the family, but are just “loose” within the family. This
29 indicates that these operators and functions are compatible with the
30 family's semantics, but are not required for correct functioning of any
31 specific index. (Operators and functions that are so required should be
32 declared as part of an operator class, instead; see CREATE OPERATOR
33 CLASS (CREATE_OPERATOR_CLASS(7)).) PostgreSQL will allow loose members
34 of a family to be dropped from the family at any time, but members of
35 an operator class cannot be dropped without dropping the whole class
36 and any indexes that depend on it. Typically, single-data-type
37 operators and functions are part of operator classes because they are
38 needed to support an index on that specific data type, while
39 cross-data-type operators and functions are made loose members of the
40 family.
41 You must be a superuser to use ALTER OPERATOR FAMILY. (This restriction
43 is made because an erroneous operator family definition could confuse
44 or even crash the server.)
45 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
49 self-consistent set. It is the user's responsibility to define a valid
50 operator family.
51 Refer to Section 35.14, “Interfacing Extensions To Indexes”, in the
53 documentation for further information.
54
56 name
57 The name (optionally schema-qualified) of an existing operator
58 family.
59 index_method
61 The name of the index method this operator family is for.
62 strategy_number
64 The index method's strategy number for an operator associated with
65 the operator family.
66 operator_name
68 The name (optionally schema-qualified) of an operator associated
69 with the operator family.
70 op_type
72 In an OPERATOR clause, the operand data type(s) of the operator, or
73 NONE to signify a left-unary or right-unary operator. Unlike the
74 comparable syntax in CREATE OPERATOR CLASS, the operand data types
75 must always be specified.
76 In an ADD FUNCTION clause, the operand data type(s) the function is
78 intended to support, if different from the input data type(s) of
79 the function. For B-tree comparison functions and hash functions it
80 is not necessary to specify op_type since the function's input data
81 type(s) are always the correct ones to use. For B-tree sort support
82 functions and all functions in GiST, SP-GiST and GIN operator
83 classes, it is necessary to specify the operand data type(s) the
84 function is to be used with.
85 In a DROP FUNCTION clause, the operand data type(s) the function is
87 intended to support must be specified.
88 sort_family_name
90 The name (optionally schema-qualified) of an existing btree
91 operator family that describes the sort ordering associated with an
92 ordering operator.
93 If neither FOR SEARCH nor FOR ORDER BY is specified, FOR SEARCH is
95 the default.
96 support_number
98 The index method's support procedure number for a function
99 associated with the operator family.
100 function_name
102 The name (optionally schema-qualified) of a function that is an
103 index method support procedure for the operator family.
104 argument_type
106 The parameter data type(s) of the function.
107 new_name
109 The new name of the operator family.
110 new_owner
112 The new owner of the operator family.
113 new_schema
115 The new schema for the operator family.
116 The OPERATOR and FUNCTION clauses can appear in any order.
118
120 Notice that the DROP syntax only specifies the “slot” in the operator
121 family, by strategy or support number and input data type(s). The name
122 of the operator or function occupying the slot is not mentioned. Also,
123 for DROP FUNCTION the type(s) to specify are the input data type(s) the
124 function is intended to support; for GiST, SP-GiST and GIN indexes this
125 might have nothing to do with the actual input argument types of the
126 function.
127 Because the index machinery does not check access permissions on
129 functions before using them, including a function or operator in an
130 operator family is tantamount to granting public execute permission on
131 it. This is usually not an issue for the sorts of functions that are
132 useful in an operator family.
133 The operators should not be defined by SQL functions. A SQL function is
135 likely to be inlined into the calling query, which will prevent the
136 optimizer from recognizing that the query matches an index.
137 Before PostgreSQL 8.4, the OPERATOR clause could include a RECHECK
139 option. This is no longer supported because whether an index operator
140 is “lossy” is now determined on-the-fly at run time. This allows
141 efficient handling of cases where an operator might or might not be
142 lossy.
143
145 The following example command adds cross-data-type operators and
146 support functions to an operator family that already contains B-tree
147 operator classes for data types int4 and int2.
148 ALTER OPERATOR FAMILY integer_ops USING btree ADD
150 -- int4 vs int2
152 OPERATOR 1 < (int4, int2) ,
153 OPERATOR 2 <= (int4, int2) ,
154 OPERATOR 3 = (int4, int2) ,
155 OPERATOR 4 >= (int4, int2) ,
156 OPERATOR 5 > (int4, int2) ,
157 FUNCTION 1 btint42cmp(int4, int2) ,
158 -- int2 vs int4
160 OPERATOR 1 < (int2, int4) ,
161 OPERATOR 2 <= (int2, int4) ,
162 OPERATOR 3 = (int2, int4) ,
163 OPERATOR 4 >= (int2, int4) ,
164 OPERATOR 5 > (int2, int4) ,
165 FUNCTION 1 btint24cmp(int2, int4) ;
166 To remove these entries again:
168 ALTER OPERATOR FAMILY integer_ops USING btree DROP
170 -- int4 vs int2
172 OPERATOR 1 (int4, int2) ,
173 OPERATOR 2 (int4, int2) ,
174 OPERATOR 3 (int4, int2) ,
175 OPERATOR 4 (int4, int2) ,
176 OPERATOR 5 (int4, int2) ,
177 FUNCTION 1 (int4, int2) ,
178 -- int2 vs int4
180 OPERATOR 1 (int2, int4) ,
181 OPERATOR 2 (int2, int4) ,
182 OPERATOR 3 (int2, int4) ,
183 OPERATOR 4 (int2, int4) ,
184 OPERATOR 5 (int2, int4) ,
185 FUNCTION 1 (int2, int4) ;
186
188 There is no ALTER OPERATOR FAMILY statement in the SQL standard.
189
191 CREATE OPERATOR FAMILY (CREATE_OPERATOR_FAMILY(7)), DROP OPERATOR
192 FAMILY (DROP_OPERATOR_FAMILY(7)), CREATE OPERATOR CLASS
193 (CREATE_OPERATOR_CLASS(7)), ALTER OPERATOR CLASS
194 (ALTER_OPERATOR_CLASS(7)), DROP OPERATOR CLASS (DROP_OPERATOR_CLASS(7))
195PostgreSQL 9.2.24 2017-11-06 ALTER OPERATOR FAMILY(7)