1ALTER OPERATOR FAMILY(7) SQL Commands ALTER OPERATOR FAMILY(7)
2
6 ALTER OPERATOR FAMILY - change the definition of an operator family
7
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
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 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 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 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 Refer to in the documentation for further information.
53
55 name The name (optionally schema-qualified) of an existing operator
56 family.
57 index_method
59 The name of the index method this operator family is for.
60 strategy_number
62 The index method's strategy number for an operator associated
63 with the operator family.
64 operator_name
66 The name (optionally schema-qualified) of an operator associated
67 with the operator family.
68 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 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 In a DROP FUNCTION clause, the operand data type(s) the function
84 is intended to support must be specified.
85 support_number
87 The index method's support procedure number for a function asso‐
88 ciated with the operator family.
89 funcname
91 The name (optionally schema-qualified) of a function that is an
92 index method support procedure for the operator family.
93 argument_types
95 The parameter data type(s) of the function.
96 newname
98 The new name of the operator family.
99 newowner
101 The new owner of the operator family.
102 The OPERATOR and FUNCTION clauses can appear in any order.
104
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 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 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 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
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 ALTER OPERATOR FAMILY integer_ops USING btree ADD
135 -- 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 -- 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 To remove these entries again:
154 ALTER OPERATOR FAMILY integer_ops USING btree DROP
156 -- 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 -- 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
175 There is no ALTER OPERATOR FAMILY statement in the SQL standard.
176
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)]
182SQL - Language Statements 2011-09-22 ALTER OPERATOR FAMILY(7)