1 -- Create the user-defined type for the 1-D integer arrays (_int4)
6 -- External C-functions for R-tree methods
11 CREATE FUNCTION _int_contains(_int4, _int4) RETURNS bool
12 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
14 COMMENT ON FUNCTION _int_contains(_int4, _int4) IS 'contains';
16 CREATE FUNCTION _int_contained(_int4, _int4) RETURNS bool
17 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
19 COMMENT ON FUNCTION _int_contained(_int4, _int4) IS 'contained in';
21 CREATE FUNCTION _int_overlap(_int4, _int4) RETURNS bool
22 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
24 COMMENT ON FUNCTION _int_overlap(_int4, _int4) IS 'overlaps';
26 CREATE FUNCTION _int_same(_int4, _int4) RETURNS bool
27 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
29 COMMENT ON FUNCTION _int_same(_int4, _int4) IS 'same as';
31 CREATE FUNCTION _int_different(_int4, _int4) RETURNS bool
32 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
34 COMMENT ON FUNCTION _int_different(_int4, _int4) IS 'different';
36 -- support routines for indexing
38 CREATE FUNCTION _int_union(_int4, _int4) RETURNS _int4
39 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
41 CREATE FUNCTION _int_inter(_int4, _int4) RETURNS _int4
42 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
49 LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_overlap,
51 RESTRICT = contsel, JOIN = contjoinsel
55 -- LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_same,
56 -- COMMUTATOR = '=', NEGATOR = '<>',
57 -- RESTRICT = eqsel, JOIN = eqjoinsel,
58 -- SORT1 = '<', SORT2 = '<'
62 LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_different,
63 COMMUTATOR = '<>', NEGATOR = '=',
64 RESTRICT = neqsel, JOIN = neqjoinsel
68 LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_contains,
69 COMMUTATOR = '~', RESTRICT = contsel, JOIN = contjoinsel
73 LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_contained,
74 COMMUTATOR = '@', RESTRICT = contsel, JOIN = contjoinsel
78 -- define the GiST support methods
79 CREATE FUNCTION g_int_consistent(opaque,_int4,int4) RETURNS bool
80 AS 'MODULE_PATHNAME' LANGUAGE 'c';
82 CREATE FUNCTION g_int_compress(opaque) RETURNS opaque
83 AS 'MODULE_PATHNAME' LANGUAGE 'c';
85 CREATE FUNCTION g_int_decompress(opaque) RETURNS opaque
86 AS 'MODULE_PATHNAME' LANGUAGE 'c';
88 CREATE FUNCTION g_int_penalty(opaque,opaque,opaque) RETURNS opaque
89 AS 'MODULE_PATHNAME' LANGUAGE 'c';
91 CREATE FUNCTION g_int_picksplit(opaque, opaque) RETURNS opaque
92 AS 'MODULE_PATHNAME' LANGUAGE 'c';
94 CREATE FUNCTION g_int_union(bytea, opaque) RETURNS _int4
95 AS 'MODULE_PATHNAME' LANGUAGE 'c';
97 CREATE FUNCTION g_int_same(_int4, _int4, opaque) RETURNS opaque
98 AS 'MODULE_PATHNAME' LANGUAGE 'c';
101 -- register the default opclass for indexing
102 INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
104 (SELECT oid FROM pg_am WHERE amname = 'gist'),
106 (SELECT oid FROM pg_type WHERE typname = '_int4'),
108 (SELECT oid FROM pg_type WHERE typname = '_int4'));
111 -- get the comparators for _intments and store them in a tmp table
112 SELECT o.oid AS opoid, o.oprname
113 INTO TEMP TABLE _int_ops_tmp
114 FROM pg_operator o, pg_type t
115 WHERE o.oprleft = t.oid and o.oprright = t.oid
116 and t.typname = '_int4';
118 -- make sure we have the right operators
119 -- SELECT * from _int_ops_tmp;
121 -- using the tmp table, generate the amop entries
122 -- note: these operators are all lossy
125 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
126 SELECT opcl.oid, 3, true, c.opoid
127 FROM pg_opclass opcl, _int_ops_tmp c
129 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
130 and opcname = 'gist__int_ops'
131 and c.oprname = '&&';
134 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
135 SELECT opcl.oid, 6, true, c.opoid
136 FROM pg_opclass opcl, _int_ops_tmp c
138 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
139 and opcname = 'gist__int_ops'
143 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
144 SELECT opcl.oid, 7, true, c.opoid
145 FROM pg_opclass opcl, _int_ops_tmp c
147 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
148 and opcname = 'gist__int_ops'
152 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
153 SELECT opcl.oid, 8, true, c.opoid
154 FROM pg_opclass opcl, _int_ops_tmp c
156 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
157 and opcname = 'gist__int_ops'
160 DROP TABLE _int_ops_tmp;
163 -- add the entries to amproc for the support methods
164 -- note the amprocnum numbers associated with each are specific!
166 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
167 SELECT opcl.oid, 1, pro.oid
168 FROM pg_opclass opcl, pg_proc pro
170 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
171 and opcname = 'gist__int_ops'
172 and proname = 'g_int_consistent';
174 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
175 SELECT opcl.oid, 2, pro.oid
176 FROM pg_opclass opcl, pg_proc pro
178 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
179 and opcname = 'gist__int_ops'
180 and proname = 'g_int_union';
182 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
183 SELECT opcl.oid, 3, pro.oid
184 FROM pg_opclass opcl, pg_proc pro
186 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
187 and opcname = 'gist__int_ops'
188 and proname = 'g_int_compress';
190 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
191 SELECT opcl.oid, 4, pro.oid
192 FROM pg_opclass opcl, pg_proc pro
194 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
195 and opcname = 'gist__int_ops'
196 and proname = 'g_int_decompress';
198 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
199 SELECT opcl.oid, 5, pro.oid
200 FROM pg_opclass opcl, pg_proc pro
202 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
203 and opcname = 'gist__int_ops'
204 and proname = 'g_int_penalty';
206 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
207 SELECT opcl.oid, 6, pro.oid
208 FROM pg_opclass opcl, pg_proc pro
210 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
211 and opcname = 'gist__int_ops'
212 and proname = 'g_int_picksplit';
214 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
215 SELECT opcl.oid, 7, pro.oid
216 FROM pg_opclass opcl, pg_proc pro
218 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
219 and opcname = 'gist__int_ops'
220 and proname = 'g_int_same';
223 ---------------------------------------------
225 ---------------------------------------------
226 -- define the GiST support methods
227 CREATE FUNCTION g_intbig_consistent(opaque,_int4,int4) RETURNS bool
228 AS 'MODULE_PATHNAME' LANGUAGE 'c';
230 CREATE FUNCTION g_intbig_compress(opaque) RETURNS opaque
231 AS 'MODULE_PATHNAME' LANGUAGE 'c';
233 CREATE FUNCTION g_intbig_decompress(opaque) RETURNS opaque
234 AS 'MODULE_PATHNAME' LANGUAGE 'c';
236 CREATE FUNCTION g_intbig_penalty(opaque,opaque,opaque) RETURNS opaque
237 AS 'MODULE_PATHNAME' LANGUAGE 'c' with (isstrict);
239 CREATE FUNCTION g_intbig_picksplit(opaque, opaque) RETURNS opaque
240 AS 'MODULE_PATHNAME' LANGUAGE 'c';
242 CREATE FUNCTION g_intbig_union(bytea, opaque) RETURNS _int4
243 AS 'MODULE_PATHNAME' LANGUAGE 'c';
245 CREATE FUNCTION g_intbig_same(_int4, _int4, opaque) RETURNS opaque
246 AS 'MODULE_PATHNAME' LANGUAGE 'c';
248 -- register the opclass for indexing (not as default)
249 INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
251 (SELECT oid FROM pg_am WHERE amname = 'gist'),
253 (SELECT oid FROM pg_type WHERE typname = '_int4'),
255 (SELECT oid FROM pg_type WHERE typname = '_int4'));
258 -- get the comparators for _intments and store them in a tmp table
259 SELECT o.oid AS opoid, o.oprname
260 INTO TEMP TABLE _int_ops_tmp
261 FROM pg_operator o, pg_type t
262 WHERE o.oprleft = t.oid and o.oprright = t.oid
263 and t.typname = '_int4';
265 -- make sure we have the right operators
266 -- SELECT * from _int_ops_tmp;
268 -- using the tmp table, generate the amop entries
269 -- note: these operators are all lossy
272 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
273 SELECT opcl.oid, 3, true, c.opoid
274 FROM pg_opclass opcl, _int_ops_tmp c
276 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
277 and opcname = 'gist__intbig_ops'
278 and c.oprname = '&&';
281 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
282 SELECT opcl.oid, 7, true, c.opoid
283 FROM pg_opclass opcl, _int_ops_tmp c
285 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
286 and opcname = 'gist__intbig_ops'
290 INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
291 SELECT opcl.oid, 8, true, c.opoid
292 FROM pg_opclass opcl, _int_ops_tmp c
294 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
295 and opcname = 'gist__intbig_ops'
298 DROP TABLE _int_ops_tmp;
301 -- add the entries to amproc for the support methods
302 -- note the amprocnum numbers associated with each are specific!
304 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
305 SELECT opcl.oid, 1, pro.oid
306 FROM pg_opclass opcl, pg_proc pro
308 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
309 and opcname = 'gist__intbig_ops'
310 and proname = 'g_intbig_consistent';
312 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
313 SELECT opcl.oid, 2, pro.oid
314 FROM pg_opclass opcl, pg_proc pro
316 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
317 and opcname = 'gist__intbig_ops'
318 and proname = 'g_intbig_union';
320 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
321 SELECT opcl.oid, 3, pro.oid
322 FROM pg_opclass opcl, pg_proc pro
324 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
325 and opcname = 'gist__intbig_ops'
326 and proname = 'g_intbig_compress';
328 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
329 SELECT opcl.oid, 4, pro.oid
330 FROM pg_opclass opcl, pg_proc pro
332 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
333 and opcname = 'gist__intbig_ops'
334 and proname = 'g_intbig_decompress';
336 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
337 SELECT opcl.oid, 5, pro.oid
338 FROM pg_opclass opcl, pg_proc pro
340 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
341 and opcname = 'gist__intbig_ops'
342 and proname = 'g_intbig_penalty';
344 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
345 SELECT opcl.oid, 6, pro.oid
346 FROM pg_opclass opcl, pg_proc pro
348 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
349 and opcname = 'gist__intbig_ops'
350 and proname = 'g_intbig_picksplit';
352 INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
353 SELECT opcl.oid, 7, pro.oid
354 FROM pg_opclass opcl, pg_proc pro
356 opcamid = (SELECT oid FROM pg_am WHERE amname = 'gist')
357 and opcname = 'gist__intbig_ops'
358 and proname = 'g_intbig_same';