🌐 AI搜索 & 代理 主页
Skip to content

Commit 65b6868

Browse files
tglsfdctglsfdc
committed
Replace ad-hoc insertions into pg_opclass and friends with CREATE
OPERATOR CLASS commands. Further tweaking of documentation for same.
1 parent ea2d974 commit 65b6868

File tree

10 files changed

+212
-1106
lines changed

10 files changed

+212
-1106
lines changed

‎contrib/btree_gist/btree_gist.sql.in‎

Lines changed: 36 additions & 187 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,9 @@
11
begin transaction;
2-
-- create type of int4 key
32

3+
-- Adjust this setting to control where the objects get created.
4+
SET search_path = public;
5+
6+
-- create type of int4 key
47
CREATE FUNCTION int4key_in(opaque)
58
RETURNS opaque
69
AS 'MODULE_PATHNAME'
@@ -40,101 +43,23 @@ create function gint4_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' lan
4043

4144
create function gint4_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C';
4245

43-
-- add a new opclass
44-
INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
45-
VALUES (
46-
(SELECT oid FROM pg_am WHERE amname = 'gist'),
47-
'gist_int4_ops',
48-
(SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
49-
1, -- UID of superuser is hardwired to 1 as of PG 7.3
50-
(SELECT oid FROM pg_type WHERE typname = 'int4'),
51-
true,
52-
(SELECT oid FROM pg_type WHERE typname = 'int4key'));
53-
54-
55-
SELECT o.oid AS opoid, o.oprname
56-
INTO TABLE int_ops_tmp
57-
FROM pg_operator o, pg_type t
58-
WHERE o.oprleft = t.oid and o.oprright = t.oid
59-
and t.typname = 'int4';
60-
61-
-- get the comparators for int4es and store them in a tmp table
62-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
63-
SELECT opcl.oid, c.opoid, 1, 'f'
64-
FROM pg_opclass opcl, int_ops_tmp c
65-
WHERE opcname = 'gist_int4_ops'
66-
and c.oprname = '<';
67-
68-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
69-
SELECT opcl.oid, c.opoid, 2, 'f'
70-
FROM pg_opclass opcl, int_ops_tmp c
71-
WHERE opcname = 'gist_int4_ops'
72-
and c.oprname = '<=';
73-
74-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
75-
SELECT opcl.oid, c.opoid, 3, 'f'
76-
FROM pg_opclass opcl, int_ops_tmp c
77-
WHERE opcname = 'gist_int4_ops'
78-
and c.oprname = '=';
79-
80-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
81-
SELECT opcl.oid, c.opoid, 4, 'f'
82-
FROM pg_opclass opcl, int_ops_tmp c
83-
WHERE opcname = 'gist_int4_ops'
84-
and c.oprname = '>=';
85-
86-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
87-
SELECT opcl.oid, c.opoid, 5, 'f'
88-
FROM pg_opclass opcl, int_ops_tmp c
89-
WHERE opcname = 'gist_int4_ops'
90-
and c.oprname = '>';
91-
92-
93-
DROP table int_ops_tmp;
94-
95-
-- add the entries to amproc for the support methods
96-
-- note the amprocnum numbers associated with each are specific!
97-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
98-
SELECT opcl.oid, pro.oid, 1
99-
FROM pg_opclass opcl, pg_proc pro
100-
WHERE opcname = 'gist_int4_ops'
101-
and proname = 'gint4_consistent';
102-
103-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
104-
SELECT opcl.oid, pro.oid, 2
105-
FROM pg_opclass opcl, pg_proc pro
106-
WHERE opcname = 'gist_int4_ops'
107-
and proname = 'gint4_union';
108-
109-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
110-
SELECT opcl.oid, pro.oid, 3
111-
FROM pg_opclass opcl, pg_proc pro
112-
WHERE opcname = 'gist_int4_ops'
113-
and proname = 'gint4_compress';
114-
115-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
116-
SELECT opcl.oid, pro.oid, 4
117-
FROM pg_opclass opcl, pg_proc pro
118-
WHERE opcname = 'gist_int4_ops'
119-
and proname = 'btree_decompress';
46+
-- Create the operator class
47+
CREATE OPERATOR CLASS gist_int4_ops
48+
DEFAULT FOR TYPE int4 USING gist AS
49+
OPERATOR 1 < ,
50+
OPERATOR 2 <= ,
51+
OPERATOR 3 = ,
52+
OPERATOR 4 >= ,
53+
OPERATOR 5 > ,
54+
FUNCTION 1 gint4_consistent (opaque, int4, int2),
55+
FUNCTION 2 gint4_union (bytea, opaque),
56+
FUNCTION 3 gint4_compress (opaque),
57+
FUNCTION 4 btree_decompress (opaque),
58+
FUNCTION 5 gint4_penalty (opaque, opaque, opaque),
59+
FUNCTION 6 gint4_picksplit (opaque, opaque),
60+
FUNCTION 7 gint4_same (opaque, opaque, opaque),
61+
STORAGE int4key;
12062

121-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
122-
SELECT opcl.oid, pro.oid, 5
123-
FROM pg_opclass opcl, pg_proc pro
124-
WHERE opcname = 'gist_int4_ops'
125-
and proname = 'gint4_penalty';
126-
127-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
128-
SELECT opcl.oid, pro.oid, 6
129-
FROM pg_opclass opcl, pg_proc pro
130-
WHERE opcname = 'gist_int4_ops'
131-
and proname = 'gint4_picksplit';
132-
133-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
134-
SELECT opcl.oid, pro.oid, 7
135-
FROM pg_opclass opcl, pg_proc pro
136-
WHERE opcname = 'gist_int4_ops'
137-
and proname = 'gint4_same';
13863

13964
--
14065
--
@@ -173,98 +98,22 @@ create function gts_union(bytea, opaque) returns int4 as 'MODULE_PATHNAME' langu
17398

17499
create function gts_same(opaque, opaque, opaque) returns opaque as 'MODULE_PATHNAME' language 'C';
175100

176-
-- add a new opclass
177-
INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype)
178-
VALUES (
179-
(SELECT oid FROM pg_am WHERE amname = 'gist'),
180-
'gist_timestamp_ops',
181-
(SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'),
182-
1, -- UID of superuser is hardwired to 1 as of PG 7.3
183-
(SELECT oid FROM pg_type WHERE typname = 'timestamp'),
184-
true,
185-
(SELECT oid FROM pg_type WHERE typname = 'tskey'));
186-
187-
SELECT o.oid AS opoid, o.oprname
188-
INTO TABLE timestamp_ops_tmp
189-
FROM pg_operator o, pg_type t
190-
WHERE o.oprleft = t.oid and o.oprright = t.oid
191-
and t.typname = 'timestamp';
192-
193-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
194-
SELECT opcl.oid, c.opoid, 1, 'f'
195-
FROM pg_opclass opcl, timestamp_ops_tmp c
196-
WHERE opcname = 'gist_timestamp_ops'
197-
and c.oprname = '<';
198-
199-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
200-
SELECT opcl.oid, c.opoid, 2, 'f'
201-
FROM pg_opclass opcl, timestamp_ops_tmp c
202-
WHERE opcname = 'gist_timestamp_ops'
203-
and c.oprname = '<=';
204-
205-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
206-
SELECT opcl.oid, c.opoid, 3, 'f'
207-
FROM pg_opclass opcl, timestamp_ops_tmp c
208-
WHERE opcname = 'gist_timestamp_ops'
209-
and c.oprname = '=';
210-
211-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
212-
SELECT opcl.oid, c.opoid, 4, 'f'
213-
FROM pg_opclass opcl, timestamp_ops_tmp c
214-
WHERE opcname = 'gist_timestamp_ops'
215-
and c.oprname = '>=';
216-
217-
INSERT INTO pg_amop (amopclaid, amopopr, amopstrategy, amopreqcheck)
218-
SELECT opcl.oid, c.opoid, 5, 'f'
219-
FROM pg_opclass opcl, timestamp_ops_tmp c
220-
WHERE opcname = 'gist_timestamp_ops'
221-
and c.oprname = '>';
101+
-- Create the operator class
102+
CREATE OPERATOR CLASS gist_timestamp_ops
103+
DEFAULT FOR TYPE timestamp USING gist AS
104+
OPERATOR 1 < ,
105+
OPERATOR 2 <= ,
106+
OPERATOR 3 = ,
107+
OPERATOR 4 >= ,
108+
OPERATOR 5 > ,
109+
FUNCTION 1 gts_consistent (opaque, timestamp, int2),
110+
FUNCTION 2 gts_union (bytea, opaque),
111+
FUNCTION 3 gts_compress (opaque),
112+
FUNCTION 4 btree_decompress (opaque),
113+
FUNCTION 5 gts_penalty (opaque, opaque, opaque),
114+
FUNCTION 6 gts_picksplit (opaque, opaque),
115+
FUNCTION 7 gts_same (opaque, opaque, opaque),
116+
STORAGE tskey;
222117

223-
DROP table timestamp_ops_tmp;
224-
225-
-- add the entries to amproc for the support methods
226-
-- note the amprocnum numbers associated with each are specific!
227-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
228-
SELECT opcl.oid, pro.oid, 1
229-
FROM pg_opclass opcl, pg_proc pro
230-
WHERE opcname = 'gist_timestamp_ops'
231-
and proname = 'gts_consistent';
232-
233-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
234-
SELECT opcl.oid, pro.oid, 2
235-
FROM pg_opclass opcl, pg_proc pro
236-
WHERE opcname = 'gist_timestamp_ops'
237-
and proname = 'gts_union';
238-
239-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
240-
SELECT opcl.oid, pro.oid, 3
241-
FROM pg_opclass opcl, pg_proc pro
242-
WHERE opcname = 'gist_timestamp_ops'
243-
and proname = 'gts_compress';
244-
245-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
246-
SELECT opcl.oid, pro.oid, 4
247-
FROM pg_opclass opcl, pg_proc pro
248-
WHERE opcname = 'gist_timestamp_ops'
249-
and proname = 'btree_decompress';
250-
251-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
252-
SELECT opcl.oid, pro.oid, 5
253-
FROM pg_opclass opcl, pg_proc pro
254-
WHERE opcname = 'gist_timestamp_ops'
255-
and proname = 'gts_penalty';
256-
257-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
258-
SELECT opcl.oid, pro.oid, 6
259-
FROM pg_opclass opcl, pg_proc pro
260-
WHERE opcname = 'gist_timestamp_ops'
261-
and proname = 'gts_picksplit';
262-
263-
INSERT INTO pg_amproc (amopclaid, amproc, amprocnum)
264-
SELECT opcl.oid, pro.oid, 7
265-
FROM pg_opclass opcl, pg_proc pro
266-
WHERE opcname = 'gist_timestamp_ops'
267-
and proname = 'gts_same';
268118

269119
end transaction;
270-

0 commit comments

Comments
 (0)