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

Commit65b6868

Browse files
committed
Replace ad-hoc insertions into pg_opclass and friends with CREATE
OPERATOR CLASS commands. Further tweaking of documentation for same.
1 parentea2d974 commit65b6868

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+
OPERATOR1< ,
50+
OPERATOR2<= ,
51+
OPERATOR3= ,
52+
OPERATOR4>= ,
53+
OPERATOR5> ,
54+
FUNCTION1gint4_consistent (opaque, int4, int2),
55+
FUNCTION2gint4_union (bytea, opaque),
56+
FUNCTION3gint4_compress (opaque),
57+
FUNCTION4btree_decompress (opaque),
58+
FUNCTION5gint4_penalty (opaque, opaque, opaque),
59+
FUNCTION6gint4_picksplit (opaque, opaque),
60+
FUNCTION7gint4_same (opaque, opaque, opaque),
61+
STORAGEint4key;
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+
OPERATOR1< ,
105+
OPERATOR2<= ,
106+
OPERATOR3= ,
107+
OPERATOR4>= ,
108+
OPERATOR5> ,
109+
FUNCTION1gts_consistent (opaque, timestamp, int2),
110+
FUNCTION2gts_union (bytea, opaque),
111+
FUNCTION3gts_compress (opaque),
112+
FUNCTION4btree_decompress (opaque),
113+
FUNCTION5gts_penalty (opaque, opaque, opaque),
114+
FUNCTION6gts_picksplit (opaque, opaque),
115+
FUNCTION7gts_same (opaque, opaque, opaque),
116+
STORAGEtskey;
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)
[8]ページ先頭
©2009-2025 Movatter.jp