Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita33cf10

Browse files
committed
Add CREATE/ALTER/DROP OPERATOR FAMILY commands, also COMMENT ON OPERATOR
FAMILY; and add FAMILY option to CREATE OPERATOR CLASS to allow adding aclass to a pre-existing family. Per previous discussion. Man, what atedious lot of cutting and pasting ...
1 parent8502b68 commita33cf10

22 files changed

+2091
-200
lines changed

‎doc/src/sgml/ref/allfiles.sgml

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.68 2006/09/18 19:54:01 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.69 2007/01/23 05:07:16 tgl Exp $
33
PostgreSQL documentation
44
Complete list of usable sgml source files in this directory.
55
-->
@@ -16,6 +16,7 @@ Complete list of usable sgml source files in this directory.
1616
<!entity alterLanguage system "alter_language.sgml">
1717
<!entity alterOperator system "alter_operator.sgml">
1818
<!entity alterOperatorClass system "alter_opclass.sgml">
19+
<!entity alterOperatorFamily system "alter_opfamily.sgml">
1920
<!entity alterRole system "alter_role.sgml">
2021
<!entity alterSchema system "alter_schema.sgml">
2122
<!entity alterSequence system "alter_sequence.sgml">
@@ -45,6 +46,7 @@ Complete list of usable sgml source files in this directory.
4546
<!entity createLanguage system "create_language.sgml">
4647
<!entity createOperator system "create_operator.sgml">
4748
<!entity createOperatorClass system "create_opclass.sgml">
49+
<!entity createOperatorFamily system "create_opfamily.sgml">
4850
<!entity createRole system "create_role.sgml">
4951
<!entity createRule system "create_rule.sgml">
5052
<!entity createSchema system "create_schema.sgml">
@@ -70,6 +72,7 @@ Complete list of usable sgml source files in this directory.
7072
<!entity dropLanguage system "drop_language.sgml">
7173
<!entity dropOperator system "drop_operator.sgml">
7274
<!entity dropOperatorClass system "drop_opclass.sgml">
75+
<!entity dropOperatorFamily system "drop_opfamily.sgml">
7376
<!entity dropOwned system "drop_owned.sgml">
7477
<!entity dropRole system "drop_role.sgml">
7578
<!entity dropRule system "drop_rule.sgml">

‎doc/src/sgml/ref/alter_opclass.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_opclass.sgml,v 1.7 2006/09/16 00:30:16 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_opclass.sgml,v 1.8 2007/01/23 05:07:17 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -102,6 +102,7 @@ ALTER OPERATOR CLASS <replaceable>name</replaceable> USING <replaceable class="p
102102
<simplelist type="inline">
103103
<member><xref linkend="sql-createopclass" endterm="sql-createopclass-title"></member>
104104
<member><xref linkend="sql-dropopclass" endterm="sql-dropopclass-title"></member>
105+
<member><xref linkend="sql-alteropfamily" endterm="sql-alteropfamily-title"></member>
105106
</simplelist>
106107
</refsect1>
107108
</refentry>

‎doc/src/sgml/ref/alter_opfamily.sgml

Lines changed: 312 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,312 @@
1+
<!--
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_opfamily.sgml,v 1.1 2007/01/23 05:07:17 tgl Exp $
3+
PostgreSQL documentation
4+
-->
5+
6+
<refentry id="SQL-ALTEROPFAMILY">
7+
<refmeta>
8+
<refentrytitle id="SQL-ALTEROPFAMILY-TITLE">ALTER OPERATOR FAMILY</refentrytitle>
9+
<refmiscinfo>SQL - Language Statements</refmiscinfo>
10+
</refmeta>
11+
12+
<refnamediv>
13+
<refname>ALTER OPERATOR FAMILY</refname>
14+
<refpurpose>change the definition of an operator family</refpurpose>
15+
</refnamediv>
16+
17+
<indexterm zone="sql-alteropfamily">
18+
<primary>ALTER OPERATOR FAMILY</primary>
19+
</indexterm>
20+
21+
<refsynopsisdiv>
22+
<synopsis>
23+
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> ADD
24+
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> ) [ RECHECK ]
25+
| FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ] <replaceable class="parameter">funcname</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] )
26+
} [, ... ]
27+
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> DROP
28+
{ OPERATOR <replaceable class="parameter">strategy_number</replaceable> ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] )
29+
| FUNCTION <replaceable class="parameter">support_number</replaceable> ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] )
30+
} [, ... ]
31+
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> RENAME TO <replaceable>newname</replaceable>
32+
ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> OWNER TO <replaceable>newowner</replaceable>
33+
</synopsis>
34+
</refsynopsisdiv>
35+
36+
<refsect1>
37+
<title>Description</title>
38+
39+
<para>
40+
<command>ALTER OPERATOR FAMILY</command> changes the definition of
41+
an operator family. You can add operators and support functions
42+
to the family, remove them from the family,
43+
or change the family's name or owner.
44+
</para>
45+
46+
<para>
47+
When operators and support functions are added to a family with
48+
<command>ALTER OPERATOR FAMILY</command>, they are not part of any
49+
specific operator class within the family, but are just <quote>loose</>
50+
within the family. This indicates that these operators and functions
51+
are compatible with the family's semantics, but are not required for
52+
correct functioning of any specific index. (Operators and functions
53+
that are so required should be declared as part of an operator class,
54+
instead; see <xref linkend="sql-createopclass"
55+
endterm="sql-createopclass-title">.)
56+
<productname>PostgreSQL</productname> will allow loose members of a
57+
family to be dropped from the family at any time, but members of an
58+
operator class cannot be dropped without dropping the whole class and
59+
any indexes that depend on it.
60+
Typically, single-data-type operators
61+
and functions are part of operator classes because they are needed to
62+
support an index on that specific data type, while cross-data-type
63+
operators and functions are made loose members of the family.
64+
</para>
65+
66+
<para>
67+
You must be a superuser to use <command>ALTER OPERATOR FAMILY</>.
68+
</para>
69+
</refsect1>
70+
71+
<refsect1>
72+
<title>Parameters</title>
73+
74+
<variablelist>
75+
<varlistentry>
76+
<term><replaceable class="parameter">name</replaceable></term>
77+
<listitem>
78+
<para>
79+
The name (optionally schema-qualified) of an existing operator
80+
family.
81+
</para>
82+
</listitem>
83+
</varlistentry>
84+
85+
<varlistentry>
86+
<term><replaceable class="parameter">index_method</replaceable></term>
87+
<listitem>
88+
<para>
89+
The name of the index method this operator family is for.
90+
</para>
91+
</listitem>
92+
</varlistentry>
93+
94+
<varlistentry>
95+
<term><replaceable class="parameter">strategy_number</replaceable></term>
96+
<listitem>
97+
<para>
98+
The index method's strategy number for an operator
99+
associated with the operator family.
100+
</para>
101+
</listitem>
102+
</varlistentry>
103+
104+
<varlistentry>
105+
<term><replaceable class="parameter">operator_name</replaceable></term>
106+
<listitem>
107+
<para>
108+
The name (optionally schema-qualified) of an operator associated
109+
with the operator family.
110+
</para>
111+
</listitem>
112+
</varlistentry>
113+
114+
<varlistentry>
115+
<term><replaceable class="parameter">op_type</replaceable></term>
116+
<listitem>
117+
<para>
118+
In an <literal>OPERATOR</> clause,
119+
the operand data type(s) of the operator, or <literal>NONE</> to
120+
signify a left-unary or right-unary operator. Unlike the comparable
121+
syntax in <command>CREATE OPERATOR CLASS</>, the operand data types
122+
must always be specified.
123+
</para>
124+
125+
<para>
126+
In an <literal>ADD FUNCTION</> clause, the operand data type(s) the
127+
function is intended to support, if different from
128+
the input data type(s) of the function. For B-tree and hash indexes
129+
it is not necessary to specify <replaceable
130+
class="parameter">op_type</replaceable> since the function's input
131+
data type(s) are always the correct ones to use. For GIN and GiST
132+
indexes it is necessary to specify the input data type the function
133+
is to be used with.
134+
</para>
135+
136+
<para>
137+
In a <literal>DROP FUNCTION</> clause, the operand data type(s) the
138+
function is intended to support must be specified.
139+
</para>
140+
</listitem>
141+
</varlistentry>
142+
143+
<varlistentry>
144+
<term><literal>RECHECK</></term>
145+
<listitem>
146+
<para>
147+
If present, the index is <quote>lossy</> for this operator, and
148+
so the rows retrieved using the index must be rechecked to
149+
verify that they actually satisfy the qualification clause
150+
involving this operator.
151+
</para>
152+
</listitem>
153+
</varlistentry>
154+
155+
<varlistentry>
156+
<term><replaceable class="parameter">support_number</replaceable></term>
157+
<listitem>
158+
<para>
159+
The index method's support procedure number for a
160+
function associated with the operator family.
161+
</para>
162+
</listitem>
163+
</varlistentry>
164+
165+
<varlistentry>
166+
<term><replaceable class="parameter">funcname</replaceable></term>
167+
<listitem>
168+
<para>
169+
The name (optionally schema-qualified) of a function that is an
170+
index method support procedure for the operator family.
171+
</para>
172+
</listitem>
173+
</varlistentry>
174+
175+
<varlistentry>
176+
<term><replaceable class="parameter">argument_types</replaceable></term>
177+
<listitem>
178+
<para>
179+
The parameter data type(s) of the function.
180+
</para>
181+
</listitem>
182+
</varlistentry>
183+
184+
<varlistentry>
185+
<term><replaceable class="parameter">newname</replaceable></term>
186+
<listitem>
187+
<para>
188+
The new name of the operator family.
189+
</para>
190+
</listitem>
191+
</varlistentry>
192+
193+
<varlistentry>
194+
<term><replaceable class="parameter">newowner</replaceable></term>
195+
<listitem>
196+
<para>
197+
The new owner of the operator family.
198+
</para>
199+
</listitem>
200+
</varlistentry>
201+
</variablelist>
202+
203+
<para>
204+
The <literal>OPERATOR</> and <literal>FUNCTION</>
205+
clauses may appear in any order.
206+
</para>
207+
208+
</refsect1>
209+
210+
<refsect1>
211+
<title>Notes</title>
212+
213+
<para>
214+
Notice that the <literal>DROP</> syntax only specifies the <quote>slot</>
215+
in the operator family, by strategy or support number and input data
216+
type(s). The name of the operator or function occupying the slot is not
217+
mentioned. Also, for <literal>DROP FUNCTION</> the type(s) to specify
218+
are the input data type(s) the function is intended to support; for
219+
GIN and GiST indexes this may have nothing to do with the actual input
220+
argument types of the function.
221+
</para>
222+
223+
<para>
224+
Because the index machinery does not check access permissions on functions
225+
before using them, including a function or operator in an operator family
226+
is tantamount to granting public execute permission on it. This is usually
227+
not an issue for the sorts of functions that are useful in an operator
228+
family.
229+
</para>
230+
231+
<para>
232+
The operators should not be defined by SQL functions. A SQL function
233+
is likely to be inlined into the calling query, which will prevent
234+
the optimizer from recognizing that the query matches an index.
235+
</para>
236+
</refsect1>
237+
238+
<refsect1>
239+
<title>Examples</title>
240+
241+
<para>
242+
The following example command adds cross-data-type operators and
243+
support functions to an operator family that already contains B-tree
244+
operator classes for data types <type>int4</> and <type>int2</>.
245+
</para>
246+
247+
<programlisting>
248+
ALTER OPERATOR FAMILY integer_ops USING btree ADD
249+
250+
-- int4 vs int2
251+
OPERATOR 1 &lt; (int4, int2) ,
252+
OPERATOR 2 &lt;= (int4, int2) ,
253+
OPERATOR 3 = (int4, int2) ,
254+
OPERATOR 4 &gt;= (int4, int2) ,
255+
OPERATOR 5 &gt; (int4, int2) ,
256+
FUNCTION 1 btint42cmp(int4, int2) ,
257+
258+
-- int2 vs int4
259+
OPERATOR 1 &lt; (int2, int4) ,
260+
OPERATOR 2 &lt;= (int2, int4) ,
261+
OPERATOR 3 = (int2, int4) ,
262+
OPERATOR 4 &gt;= (int2, int4) ,
263+
OPERATOR 5 &gt; (int2, int4) ,
264+
FUNCTION 1 btint24cmp(int2, int4) ;
265+
</programlisting>
266+
267+
<para>
268+
To remove these entries again:
269+
</para>
270+
271+
<programlisting>
272+
ALTER OPERATOR FAMILY integer_ops USING btree DROP
273+
274+
-- int4 vs int2
275+
OPERATOR 1 (int4, int2) ,
276+
OPERATOR 2 (int4, int2) ,
277+
OPERATOR 3 (int4, int2) ,
278+
OPERATOR 4 (int4, int2) ,
279+
OPERATOR 5 (int4, int2) ,
280+
FUNCTION 1 (int4, int2) ,
281+
282+
-- int2 vs int4
283+
OPERATOR 1 (int2, int4) ,
284+
OPERATOR 2 (int2, int4) ,
285+
OPERATOR 3 (int2, int4) ,
286+
OPERATOR 4 (int2, int4) ,
287+
OPERATOR 5 (int2, int4) ,
288+
FUNCTION 1 (int2, int4) ;
289+
</programlisting>
290+
</refsect1>
291+
292+
<refsect1>
293+
<title>Compatibility</title>
294+
295+
<para>
296+
There is no <command>ALTER OPERATOR FAMILY</command> statement in
297+
the SQL standard.
298+
</para>
299+
</refsect1>
300+
301+
<refsect1>
302+
<title>See Also</title>
303+
304+
<simplelist type="inline">
305+
<member><xref linkend="sql-createopfamily" endterm="sql-createopfamily-title"></member>
306+
<member><xref linkend="sql-dropopfamily" endterm="sql-dropopfamily-title"></member>
307+
<member><xref linkend="sql-createopclass" endterm="sql-createopclass-title"></member>
308+
<member><xref linkend="sql-alteropclass" endterm="sql-alteropclass-title"></member>
309+
<member><xref linkend="sql-dropopclass" endterm="sql-dropopclass-title"></member>
310+
</simplelist>
311+
</refsect1>
312+
</refentry>

‎doc/src/sgml/ref/comment.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.33 2006/10/2318:10:32 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/comment.sgml,v 1.34 2007/01/2305:07:17 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -35,6 +35,7 @@ COMMENT ON
3535
LARGE OBJECT <replaceable class="PARAMETER">large_object_oid</replaceable> |
3636
OPERATOR <replaceable class="PARAMETER">op</replaceable> (<replaceable class="PARAMETER">leftoperand_type</replaceable>, <replaceable class="PARAMETER">rightoperand_type</replaceable>) |
3737
OPERATOR CLASS <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
38+
OPERATOR FAMILY <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
3839
[ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> |
3940
ROLE <replaceable class="PARAMETER">object_name</replaceable> |
4041
RULE <replaceable class="PARAMETER">rule_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
@@ -92,7 +93,7 @@ COMMENT ON
9293
<para>
9394
The name of the object to be commented. Names of tables,
9495
aggregates, domains, functions, indexes, operators, operator classes,
95-
sequences, types, and views may be schema-qualified.
96+
operator families,sequences, types, and views may be schema-qualified.
9697
</para>
9798
</listitem>
9899
</varlistentry>
@@ -247,6 +248,7 @@ COMMENT ON LARGE OBJECT 346344 IS 'Planning document';
247248
COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts';
248249
COMMENT ON OPERATOR - (NONE, text) IS 'This is a prefix operator on text';
249250
COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees';
251+
COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees';
250252
COMMENT ON ROLE my_role IS 'Administration group for finance tables';
251253
COMMENT ON RULE my_rule ON my_table IS 'Logs updates of employee records';
252254
COMMENT ON SCHEMA my_schema IS 'Departmental data';

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp