Skip to content

Commit ea4686e

Browse files
tglsfdctglsfdc
committed
Implement CREATE/DROP OPERATOR CLASS. Work still remains: need more
documentation (xindex.sgml should be rewritten), need to teach pg_dump about it, need to update contrib modules that currently build pg_opclass entries by hand. Original patch by Bill Studenmund, grammar adjustments and general update for 7.3 by Tom Lane.
1 parent b9459c6 commit ea4686e

File tree

26 files changed

+1697
-373
lines changed

26 files changed

+1697
-373
lines changed

‎contrib/intarray/_int.sql.in

Lines changed: 35 additions & 262 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,10 @@
22
--
33
BEGIN TRANSACTION;
44

5+
-- Adjust this setting to control where the operators, functions, and
6+
-- opclasses get created.
7+
SET search_path = public;
8+
59
-- Query type
610
CREATE FUNCTION bqarr_in(opaque)
711
RETURNS opaque
@@ -143,137 +147,22 @@ CREATE FUNCTION g_int_same(_int4, _int4, opaque) RETURNS opaque
143147
AS 'MODULE_PATHNAME' LANGUAGE 'c';
144148

145149

146-
-- register the default opclass for indexing
147-
INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
148-
VALUES (
149-
(SELECT oid FROM pg_am WHERE amname = 'gist'),
150-
'gist__int_ops',
151-
(SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
152-
1, -- UID of superuser is hardwired to 1 as of PG 7.3
153-
(SELECT oid FROM pg_type WHERE typname = '_int4'),
154-
true,
155-
0);
156-
157-
158-
-- get the comparators for _intments and store them in a tmp table
159-
SELECT o.oid AS opoid, o.oprname
160-
INTO TEMP TABLE _int_ops_tmp
161-
FROM pg_operator o, pg_type t, pg_type tq
162-
WHERE o.oprleft = t.oid and ( o.oprright = t.oid or o.oprright=tq.oid )
163-
and t.typname = '_int4'
164-
and tq.typname='query_int';
165-
166-
-- make sure we have the right operators
167-
-- SELECT * from _int_ops_tmp;
168-
169-
-- using the tmp table, generate the amop entries
170-
171-
-- _int_overlap
172-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
173-
SELECT opcl.oid, 3, false, c.opoid
174-
FROM pg_opclass opcl, _int_ops_tmp c
175-
WHERE
176-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
177-
and opcname = 'gist__int_ops'
178-
and c.oprname = '&&';
179-
180-
-- _int_same
181-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
182-
SELECT opcl.oid, 6, true, c.opoid
183-
FROM pg_opclass opcl, _int_ops_tmp c
184-
WHERE
185-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
186-
and opcname = 'gist__int_ops'
187-
and c.oprname = '=';
188-
189-
-- _int_contains
190-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
191-
SELECT opcl.oid, 7, false, c.opoid
192-
FROM pg_opclass opcl, _int_ops_tmp c
193-
WHERE
194-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
195-
and opcname = 'gist__int_ops'
196-
and c.oprname = '@';
197-
198-
-- _int_contained
199-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
200-
SELECT opcl.oid, 8, false, c.opoid
201-
FROM pg_opclass opcl, _int_ops_tmp c
202-
WHERE
203-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
204-
and opcname = 'gist__int_ops'
205-
and c.oprname = '~';
206-
207-
--boolean search
208-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
209-
SELECT opcl.oid, 20, false, c.opoid
210-
FROM pg_opclass opcl, _int_ops_tmp c
211-
WHERE
212-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
213-
and opcname = 'gist__int_ops'
214-
and c.oprname = '@@';
215-
216-
DROP TABLE _int_ops_tmp;
217-
218-
219-
-- add the entries to amproc for the support methods
220-
-- note the amprocnum numbers associated with each are specific!
221-
222-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
223-
SELECT opcl.oid, 1, pro.oid
224-
FROM pg_opclass opcl, pg_proc pro
225-
WHERE
226-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
227-
and opcname = 'gist__int_ops'
228-
and proname = 'g_int_consistent';
229-
230-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
231-
SELECT opcl.oid, 2, pro.oid
232-
FROM pg_opclass opcl, pg_proc pro
233-
WHERE
234-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
235-
and opcname = 'gist__int_ops'
236-
and proname = 'g_int_union';
237-
238-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
239-
SELECT opcl.oid, 3, pro.oid
240-
FROM pg_opclass opcl, pg_proc pro
241-
WHERE
242-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
243-
and opcname = 'gist__int_ops'
244-
and proname = 'g_int_compress';
245-
246-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
247-
SELECT opcl.oid, 4, pro.oid
248-
FROM pg_opclass opcl, pg_proc pro
249-
WHERE
250-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
251-
and opcname = 'gist__int_ops'
252-
and proname = 'g_int_decompress';
253-
254-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
255-
SELECT opcl.oid, 5, pro.oid
256-
FROM pg_opclass opcl, pg_proc pro
257-
WHERE
258-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
259-
and opcname = 'gist__int_ops'
260-
and proname = 'g_int_penalty';
261-
262-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
263-
SELECT opcl.oid, 6, pro.oid
264-
FROM pg_opclass opcl, pg_proc pro
265-
WHERE
266-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
267-
and opcname = 'gist__int_ops'
268-
and proname = 'g_int_picksplit';
269-
270-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
271-
SELECT opcl.oid, 7, pro.oid
272-
FROM pg_opclass opcl, pg_proc pro
273-
WHERE
274-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
275-
and opcname = 'gist__int_ops'
276-
and proname = 'g_int_same';
150+
-- Create the operator class for indexing
151+
152+
CREATE OPERATOR CLASS gist__int_ops
153+
DEFAULT FOR TYPE _int4 USING gist AS
154+
OPERATOR 3 &&,
155+
OPERATOR 6 = RECHECK,
156+
OPERATOR 7 @,
157+
OPERATOR 8 ~,
158+
OPERATOR 20 @@ (_int4, query_int),
159+
FUNCTION 1 g_int_consistent (opaque, _int4, int4),
160+
FUNCTION 2 g_int_union (bytea, opaque),
161+
FUNCTION 3 g_int_compress (opaque),
162+
FUNCTION 4 g_int_decompress (opaque),
163+
FUNCTION 5 g_int_penalty (opaque, opaque, opaque),
164+
FUNCTION 6 g_int_picksplit (opaque, opaque),
165+
FUNCTION 7 g_int_same (_int4, _int4, opaque);
277166

278167

279168
---------------------------------------------
@@ -302,136 +191,20 @@ CREATE FUNCTION g_intbig_same(_int4, _int4, opaque) RETURNS opaque
302191
AS 'MODULE_PATHNAME' LANGUAGE 'c';
303192

304193
-- register the opclass for indexing (not as default)
305-
INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
306-
VALUES (
307-
(SELECT oid FROM pg_am WHERE amname = 'gist'),
308-
'gist__intbig_ops',
309-
(SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
310-
1, -- UID of superuser is hardwired to 1 as of PG 7.3
311-
(SELECT oid FROM pg_type WHERE typname = '_int4'),
312-
false,
313-
0);
314-
315-
316-
-- get the comparators for _intments and store them in a tmp table
317-
SELECT o.oid AS opoid, o.oprname
318-
INTO TEMP TABLE _int_ops_tmp
319-
FROM pg_operator o, pg_type t, pg_type tq
320-
WHERE o.oprleft = t.oid and ( o.oprright = t.oid or o.oprright=tq.oid )
321-
and t.typname = '_int4'
322-
and tq.typname='query_int';
323-
324-
-- make sure we have the right operators
325-
-- SELECT * from _int_ops_tmp;
326-
327-
-- using the tmp table, generate the amop entries
328-
-- note: these operators are all lossy
329-
330-
-- _int_overlap
331-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
332-
SELECT opcl.oid, 3, true, c.opoid
333-
FROM pg_opclass opcl, _int_ops_tmp c
334-
WHERE
335-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
336-
and opcname = 'gist__intbig_ops'
337-
and c.oprname = '&&';
338-
339-
-- _int_contains
340-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
341-
SELECT opcl.oid, 7, true, c.opoid
342-
FROM pg_opclass opcl, _int_ops_tmp c
343-
WHERE
344-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
345-
and opcname = 'gist__intbig_ops'
346-
and c.oprname = '@';
347-
348-
-- _int_contained
349-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
350-
SELECT opcl.oid, 8, true, c.opoid
351-
FROM pg_opclass opcl, _int_ops_tmp c
352-
WHERE
353-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
354-
and opcname = 'gist__intbig_ops'
355-
and c.oprname = '~';
356-
357-
-- _int_same
358-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
359-
SELECT opcl.oid, 6, true, c.opoid
360-
FROM pg_opclass opcl, _int_ops_tmp c
361-
WHERE
362-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
363-
and opcname = 'gist__intbig_ops'
364-
and c.oprname = '=';
365-
366-
--boolean search
367-
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
368-
SELECT opcl.oid, 20, true, c.opoid
369-
FROM pg_opclass opcl, _int_ops_tmp c
370-
WHERE
371-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
372-
and opcname = 'gist__intbig_ops'
373-
and c.oprname = '@@';
374-
375-
DROP TABLE _int_ops_tmp;
376-
377-
378-
-- add the entries to amproc for the support methods
379-
-- note the amprocnum numbers associated with each are specific!
380-
381-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
382-
SELECT opcl.oid, 1, pro.oid
383-
FROM pg_opclass opcl, pg_proc pro
384-
WHERE
385-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
386-
and opcname = 'gist__intbig_ops'
387-
and proname = 'g_intbig_consistent';
388-
389-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
390-
SELECT opcl.oid, 2, pro.oid
391-
FROM pg_opclass opcl, pg_proc pro
392-
WHERE
393-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
394-
and opcname = 'gist__intbig_ops'
395-
and proname = 'g_intbig_union';
396-
397-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
398-
SELECT opcl.oid, 3, pro.oid
399-
FROM pg_opclass opcl, pg_proc pro
400-
WHERE
401-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
402-
and opcname = 'gist__intbig_ops'
403-
and proname = 'g_intbig_compress';
404-
405-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
406-
SELECT opcl.oid, 4, pro.oid
407-
FROM pg_opclass opcl, pg_proc pro
408-
WHERE
409-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
410-
and opcname = 'gist__intbig_ops'
411-
and proname = 'g_intbig_decompress';
412-
413-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
414-
SELECT opcl.oid, 5, pro.oid
415-
FROM pg_opclass opcl, pg_proc pro
416-
WHERE
417-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
418-
and opcname = 'gist__intbig_ops'
419-
and proname = 'g_intbig_penalty';
420-
421-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
422-
SELECT opcl.oid, 6, pro.oid
423-
FROM pg_opclass opcl, pg_proc pro
424-
WHERE
425-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
426-
and opcname = 'gist__intbig_ops'
427-
and proname = 'g_intbig_picksplit';
428-
429-
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
430-
SELECT opcl.oid, 7, pro.oid
431-
FROM pg_opclass opcl, pg_proc pro
432-
WHERE
433-
opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
434-
and opcname = 'gist__intbig_ops'
435-
and proname = 'g_intbig_same';
194+
195+
CREATE OPERATOR CLASS gist__intbig_ops
196+
FOR TYPE _int4 USING gist AS
197+
OPERATOR 3 && RECHECK,
198+
OPERATOR 6 = RECHECK,
199+
OPERATOR 7 @ RECHECK,
200+
OPERATOR 8 ~ RECHECK,
201+
OPERATOR 20 @@ (_int4, query_int) RECHECK,
202+
FUNCTION 1 g_intbig_consistent (opaque, _int4, int4),
203+
FUNCTION 2 g_intbig_union (bytea, opaque),
204+
FUNCTION 3 g_intbig_compress (opaque),
205+
FUNCTION 4 g_intbig_decompress (opaque),
206+
FUNCTION 5 g_intbig_penalty (opaque, opaque, opaque),
207+
FUNCTION 6 g_intbig_picksplit (opaque, opaque),
208+
FUNCTION 7 g_intbig_same (_int4, _int4, opaque);
436209

437210
END TRANSACTION;

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

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.42 2002/07/22 08:57:15 ishii Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.43 2002/07/29 22:14:10 tgl Exp $
33
PostgreSQL documentation
44
Complete list of usable sgml source files in this directory.
55
-->
@@ -61,6 +61,7 @@ Complete list of usable sgml source files in this directory.
6161
<!entity createIndex system "create_index.sgml">
6262
<!entity createLanguage system "create_language.sgml">
6363
<!entity createOperator system "create_operator.sgml">
64+
<!entity createOperatorClass system "create_opclass.sgml">
6465
<!entity createRule system "create_rule.sgml">
6566
<!entity createSchema system "create_schema.sgml">
6667
<!entity createSequence system "create_sequence.sgml">
@@ -82,6 +83,7 @@ Complete list of usable sgml source files in this directory.
8283
<!entity dropIndex system "drop_index.sgml">
8384
<!entity dropLanguage system "drop_language.sgml">
8485
<!entity dropOperator system "drop_operator.sgml">
86+
<!entity dropOperatorClass system "drop_opclass.sgml">
8587
<!entity dropRule system "drop_rule.sgml">
8688
<!entity dropSchema system "drop_schema.sgml">
8789
<!entity dropSequence system "drop_sequence.sgml">

0 commit comments

Comments
 (0)