1 | package tuffy.db; |
2 | |
3 | import java.util.ArrayList; |
4 | |
5 | import org.apache.commons.lang3.StringEscapeUtils; |
6 | /** |
7 | * Container of SQL related utilities. |
8 | */ |
9 | public class SQLMan { |
10 | |
11 | /** |
12 | * blue --> E'blue' |
13 | * John's hat --> E'John\'s hat' |
14 | * key\tdata --> E'key\tdata' |
15 | * @param s |
16 | */ |
17 | public static String escapeString(String s){ |
18 | return "E'" + StringEscapeUtils.escapeJava(s) |
19 | .replace("'", "\\'") + "'"; |
20 | } |
21 | |
22 | public static String escapeStringNoE(String s){ |
23 | return StringEscapeUtils.escapeJava(s) |
24 | .replace("'", "\\'"); |
25 | } |
26 | |
27 | public static String procTail() { |
28 | return "\n$$ LANGUAGE 'plpgsql'"; |
29 | } |
30 | |
31 | public static String seqNext(String seq) { |
32 | return "nextval('" + seq + "')"; |
33 | } |
34 | |
35 | public static String seqCurr(String seq) { |
36 | return "currval('" + seq + "')"; |
37 | } |
38 | |
39 | public static String funcHead(String pname) { |
40 | return "CREATE OR REPLACE FUNCTION " + pname + |
41 | "() RETURNS VOID AS $$\n"; |
42 | } |
43 | |
44 | public static String funcTail() { |
45 | return "\n$$ LANGUAGE 'plpgsql'"; |
46 | } |
47 | |
48 | public static String indexName(String object, String tag) { |
49 | return "idx_" + object + "_" + tag; |
50 | } |
51 | |
52 | public static String seqName(String object) { |
53 | return "seq_" + object; |
54 | } |
55 | |
56 | public static String procName(String object, String tag) { |
57 | return "proc_" + object + "_" + tag; |
58 | } |
59 | |
60 | public static String negSelCond(String cond) { |
61 | return "NOT (" + cond + " )"; |
62 | } |
63 | |
64 | public static String andSelCond(String a, String b) { |
65 | return "(" + a + ") AND (" + b + ")"; |
66 | } |
67 | |
68 | public static String orSelCond(String a, String b) { |
69 | return "(" + a + ") OR (" + b + ")"; |
70 | } |
71 | |
72 | public static String andSelCond(ArrayList<String> conds) { |
73 | StringBuilder s = new StringBuilder(); |
74 | for(int i=0; i<conds.size(); i++) { |
75 | s.append("(" + conds.get(i) + ")"); |
76 | if(i != conds.size()-1) s.append(" AND "); |
77 | } |
78 | return s.toString(); |
79 | } |
80 | |
81 | public static String orSelCond(ArrayList<String> conds) { |
82 | StringBuilder s = new StringBuilder(); |
83 | for(int i=0; i<conds.size(); i++) { |
84 | s.append("(" + conds.get(i) + ")"); |
85 | if(i != conds.size()-1) s.append(" OR "); |
86 | } |
87 | return s.toString(); |
88 | } |
89 | |
90 | public static String sqlTypeConversions = |
91 | "\r\n" + |
92 | "CREATE OR REPLACE FUNCTION convert_to_integer(v_input anyelement)\r\n" + |
93 | "RETURNS INTEGER AS $$\r\n" + |
94 | "DECLARE v_out_value INTEGER DEFAULT NULL;\r\n" + |
95 | "BEGIN\r\n" + |
96 | " BEGIN\r\n" + |
97 | " v_out_value := v_input::INTEGER;\r\n" + |
98 | " EXCEPTION WHEN OTHERS THEN\r\n" + |
99 | " RETURN NULL;\r\n" + |
100 | " END;\r\n" + |
101 | "RETURN v_out_value;\r\n" + |
102 | "END;\r\n" + |
103 | "$$ LANGUAGE plpgsql;\r\n" + |
104 | "\r\n" + |
105 | "CREATE OR REPLACE FUNCTION convert_to_float(v_input anyelement)\r\n" + |
106 | "RETURNS FLOAT AS $$\r\n" + |
107 | "DECLARE v_out_value FLOAT DEFAULT NULL;\r\n" + |
108 | "BEGIN\r\n" + |
109 | " BEGIN\r\n" + |
110 | " v_out_value := v_input::FLOAT;\r\n" + |
111 | " EXCEPTION WHEN OTHERS THEN\r\n" + |
112 | " RETURN NULL;\r\n" + |
113 | " END;\r\n" + |
114 | "RETURN v_out_value;\r\n" + |
115 | "END;\r\n" + |
116 | "$$ LANGUAGE plpgsql;\r\n" + |
117 | "\r\n" + |
118 | "CREATE OR REPLACE FUNCTION convert_to_bool(v_input anyelement)\r\n" + |
119 | "RETURNS BOOL AS $$\r\n" + |
120 | "DECLARE v_out_value BOOL DEFAULT NULL;\r\n" + |
121 | "BEGIN\r\n" + |
122 | " BEGIN\r\n" + |
123 | " v_out_value := v_input::BOOL;\r\n" + |
124 | " EXCEPTION WHEN OTHERS THEN\r\n" + |
125 | " RETURN NULL;\r\n" + |
126 | " END;\r\n" + |
127 | "RETURN v_out_value;\r\n" + |
128 | "END;\r\n" + |
129 | "$$ LANGUAGE plpgsql;\r\n" + |
130 | "CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS integer AS $$" + |
131 | "DECLARE "+ |
132 | " result int; "+ |
133 | " BEGIN "+ |
134 | " EXECUTE 'SELECT x''' || hexval || '''::int' INTO result;" + |
135 | " RETURN result; "+ |
136 | " END; "+ |
137 | " $$ "+ |
138 | " LANGUAGE 'plpgsql' IMMUTABLE STRICT;\r\n "; |
139 | |
140 | public static String sqlFuncMisc = |
141 | "create or replace function isnum(text) returns boolean as '\r\n" + |
142 | "select $1 ~ ''^(-)?[0-9]+(.[0-9]+)?$'' as result\r\n" + |
143 | "' language sql;"; |
144 | |
145 | public static String sqlRandomAgg = |
146 | "CREATE OR REPLACE FUNCTION _random_element(anyarray) \r\n" + |
147 | " RETURNS anyelement AS\r\n" + |
148 | "$BODY$\r\n" + |
149 | " SELECT $1[array_lower($1,1) + floor((1 + array_upper($1, 1) - array_lower($1, 1))*random())];\r\n" + |
150 | "$BODY$\r\n" + |
151 | "LANGUAGE 'sql' IMMUTABLE;\r\n" + |
152 | " \r\n" + |
153 | "CREATE AGGREGATE random(anyelement) (\r\n" + |
154 | " SFUNC=array_append, --Function to call for each row. Just builds the array\r\n" + |
155 | " STYPE=anyarray,\r\n" + |
156 | " FINALFUNC=_random_element, --Function to call after everything has been added to array\r\n" + |
157 | " INITCOND='{}' --Initialize an empty array when starting\r\n" + |
158 | ");"; |
159 | |
160 | public static String sqlIntArrayFuncReg = "\r\n" + |
161 | "-- Create the user-defined type for the 1-D integer arrays (_int4)\r\n" + |
162 | "--\r\n" + |
163 | "\r\n" + |
164 | "-- Query type\r\n" + |
165 | "CREATE OR REPLACE FUNCTION bqarr_in(cstring)\r\n" + |
166 | "RETURNS query_int\r\n" + |
167 | "AS '$libdir/_int'\r\n" + |
168 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
169 | "\r\n" + |
170 | "CREATE OR REPLACE FUNCTION bqarr_out(query_int)\r\n" + |
171 | "RETURNS cstring\r\n" + |
172 | "AS '$libdir/_int'\r\n" + |
173 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
174 | "\r\n" + |
175 | "CREATE TYPE query_int (\r\n" + |
176 | " INTERNALLENGTH = -1,\r\n" + |
177 | " INPUT = bqarr_in,\r\n" + |
178 | " OUTPUT = bqarr_out\r\n" + |
179 | ");\r\n" + |
180 | "\r\n" + |
181 | "--only for debug\r\n" + |
182 | "CREATE OR REPLACE FUNCTION querytree(query_int)\r\n" + |
183 | "RETURNS text\r\n" + |
184 | "AS '$libdir/_int'\r\n" + |
185 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
186 | "\r\n" + |
187 | "\r\n" + |
188 | "CREATE OR REPLACE FUNCTION boolop(_int4, query_int)\r\n" + |
189 | "RETURNS bool\r\n" + |
190 | "AS '$libdir/_int'\r\n" + |
191 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
192 | "\r\n" + |
193 | "COMMENT ON FUNCTION boolop(_int4, query_int) IS 'boolean operation with array';\r\n" + |
194 | "\r\n" + |
195 | "CREATE OR REPLACE FUNCTION rboolop(query_int, _int4)\r\n" + |
196 | "RETURNS bool\r\n" + |
197 | "AS '$libdir/_int'\r\n" + |
198 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
199 | "\r\n" + |
200 | "COMMENT ON FUNCTION rboolop(query_int, _int4) IS 'boolean operation with array';\r\n" + |
201 | "\r\n" + |
202 | "CREATE OPERATOR @@ (\r\n" + |
203 | " LEFTARG = _int4,\r\n" + |
204 | " RIGHTARG = query_int,\r\n" + |
205 | " PROCEDURE = boolop,\r\n" + |
206 | " COMMUTATOR = '~~',\r\n" + |
207 | " RESTRICT = contsel,\r\n" + |
208 | " JOIN = contjoinsel\r\n" + |
209 | ");\r\n" + |
210 | "\r\n" + |
211 | "CREATE OPERATOR ~~ (\r\n" + |
212 | " LEFTARG = query_int,\r\n" + |
213 | " RIGHTARG = _int4,\r\n" + |
214 | " PROCEDURE = rboolop,\r\n" + |
215 | " COMMUTATOR = '@@',\r\n" + |
216 | " RESTRICT = contsel,\r\n" + |
217 | " JOIN = contjoinsel\r\n" + |
218 | ");\r\n" + |
219 | "\r\n" + |
220 | "\r\n" + |
221 | "--\r\n" + |
222 | "-- External C-functions for R-tree methods\r\n" + |
223 | "--\r\n" + |
224 | "\r\n" + |
225 | "-- Comparison methods\r\n" + |
226 | "\r\n" + |
227 | "CREATE OR REPLACE FUNCTION _int_contains(_int4, _int4)\r\n" + |
228 | "RETURNS bool\r\n" + |
229 | "AS '$libdir/_int'\r\n" + |
230 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
231 | "\r\n" + |
232 | "COMMENT ON FUNCTION _int_contains(_int4, _int4) IS 'contains';\r\n" + |
233 | "\r\n" + |
234 | "CREATE OR REPLACE FUNCTION _int_contained(_int4, _int4)\r\n" + |
235 | "RETURNS bool\r\n" + |
236 | "AS '$libdir/_int'\r\n" + |
237 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
238 | "\r\n" + |
239 | "COMMENT ON FUNCTION _int_contained(_int4, _int4) IS 'contained in';\r\n" + |
240 | "\r\n" + |
241 | "CREATE OR REPLACE FUNCTION _int_overlap(_int4, _int4)\r\n" + |
242 | "RETURNS bool\r\n" + |
243 | "AS '$libdir/_int'\r\n" + |
244 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
245 | "\r\n" + |
246 | "COMMENT ON FUNCTION _int_overlap(_int4, _int4) IS 'overlaps';\r\n" + |
247 | "\r\n" + |
248 | "CREATE OR REPLACE FUNCTION _int_same(_int4, _int4)\r\n" + |
249 | "RETURNS bool\r\n" + |
250 | "AS '$libdir/_int'\r\n" + |
251 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
252 | "\r\n" + |
253 | "COMMENT ON FUNCTION _int_same(_int4, _int4) IS 'same as';\r\n" + |
254 | "\r\n" + |
255 | "CREATE OR REPLACE FUNCTION _int_different(_int4, _int4)\r\n" + |
256 | "RETURNS bool\r\n" + |
257 | "AS '$libdir/_int'\r\n" + |
258 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
259 | "\r\n" + |
260 | "COMMENT ON FUNCTION _int_different(_int4, _int4) IS 'different';\r\n" + |
261 | "\r\n" + |
262 | "-- support routines for indexing\r\n" + |
263 | "\r\n" + |
264 | "CREATE OR REPLACE FUNCTION _int_union(_int4, _int4)\r\n" + |
265 | "RETURNS _int4\r\n" + |
266 | "AS '$libdir/_int'\r\n" + |
267 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
268 | "\r\n" + |
269 | "CREATE OR REPLACE FUNCTION _int_inter(_int4, _int4)\r\n" + |
270 | "RETURNS _int4\r\n" + |
271 | "AS '$libdir/_int'\r\n" + |
272 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
273 | "\r\n" + |
274 | "--\r\n" + |
275 | "-- OPERATORS\r\n" + |
276 | "--\r\n" + |
277 | "\r\n" + |
278 | "CREATE OPERATOR && (\r\n" + |
279 | " LEFTARG = _int4,\r\n" + |
280 | " RIGHTARG = _int4,\r\n" + |
281 | " PROCEDURE = _int_overlap,\r\n" + |
282 | " COMMUTATOR = '&&',\r\n" + |
283 | " RESTRICT = contsel,\r\n" + |
284 | " JOIN = contjoinsel\r\n" + |
285 | ");\r\n" + |
286 | "\r\n" + |
287 | "--CREATE OPERATOR = (\r\n" + |
288 | "-- LEFTARG = _int4,\r\n" + |
289 | "-- RIGHTARG = _int4,\r\n" + |
290 | "-- PROCEDURE = _int_same,\r\n" + |
291 | "-- COMMUTATOR = '=',\r\n" + |
292 | "-- NEGATOR = '<>',\r\n" + |
293 | "-- RESTRICT = eqsel,\r\n" + |
294 | "-- JOIN = eqjoinsel,\r\n" + |
295 | "-- SORT1 = '<',\r\n" + |
296 | "-- SORT2 = '<'\r\n" + |
297 | "--);\r\n" + |
298 | "\r\n" + |
299 | "--CREATE OPERATOR <> (\r\n" + |
300 | "-- LEFTARG = _int4,\r\n" + |
301 | "-- RIGHTARG = _int4,\r\n" + |
302 | "-- PROCEDURE = _int_different,\r\n" + |
303 | "-- COMMUTATOR = '<>',\r\n" + |
304 | "-- NEGATOR = '=',\r\n" + |
305 | "-- RESTRICT = neqsel,\r\n" + |
306 | "-- JOIN = neqjoinsel\r\n" + |
307 | "--);\r\n" + |
308 | "\r\n" + |
309 | "CREATE OPERATOR @> (\r\n" + |
310 | " LEFTARG = _int4,\r\n" + |
311 | " RIGHTARG = _int4,\r\n" + |
312 | " PROCEDURE = _int_contains,\r\n" + |
313 | " COMMUTATOR = '<@',\r\n" + |
314 | " RESTRICT = contsel,\r\n" + |
315 | " JOIN = contjoinsel\r\n" + |
316 | ");\r\n" + |
317 | "\r\n" + |
318 | "CREATE OPERATOR <@ (\r\n" + |
319 | " LEFTARG = _int4,\r\n" + |
320 | " RIGHTARG = _int4,\r\n" + |
321 | " PROCEDURE = _int_contained,\r\n" + |
322 | " COMMUTATOR = '@>',\r\n" + |
323 | " RESTRICT = contsel,\r\n" + |
324 | " JOIN = contjoinsel\r\n" + |
325 | ");\r\n" + |
326 | "\r\n" + |
327 | "-- obsolete:\r\n" + |
328 | "CREATE OPERATOR @ (\r\n" + |
329 | " LEFTARG = _int4,\r\n" + |
330 | " RIGHTARG = _int4,\r\n" + |
331 | " PROCEDURE = _int_contains,\r\n" + |
332 | " COMMUTATOR = '~',\r\n" + |
333 | " RESTRICT = contsel,\r\n" + |
334 | " JOIN = contjoinsel\r\n" + |
335 | ");\r\n" + |
336 | "\r\n" + |
337 | "CREATE OPERATOR ~ (\r\n" + |
338 | " LEFTARG = _int4,\r\n" + |
339 | " RIGHTARG = _int4,\r\n" + |
340 | " PROCEDURE = _int_contained,\r\n" + |
341 | " COMMUTATOR = '@',\r\n" + |
342 | " RESTRICT = contsel,\r\n" + |
343 | " JOIN = contjoinsel\r\n" + |
344 | ");\r\n" + |
345 | "\r\n" + |
346 | "--------------\r\n" + |
347 | "CREATE OR REPLACE FUNCTION intset(int4)\r\n" + |
348 | "RETURNS _int4\r\n" + |
349 | "AS '$libdir/_int'\r\n" + |
350 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
351 | "\r\n" + |
352 | "CREATE OR REPLACE FUNCTION icount(_int4)\r\n" + |
353 | "RETURNS int4\r\n" + |
354 | "AS '$libdir/_int'\r\n" + |
355 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
356 | "\r\n" + |
357 | "CREATE OPERATOR # (\r\n" + |
358 | " RIGHTARG = _int4,\r\n" + |
359 | " PROCEDURE = icount\r\n" + |
360 | ");\r\n" + |
361 | "\r\n" + |
362 | "CREATE OR REPLACE FUNCTION sort(_int4, text)\r\n" + |
363 | "RETURNS _int4\r\n" + |
364 | "AS '$libdir/_int'\r\n" + |
365 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
366 | "\r\n" + |
367 | "CREATE OR REPLACE FUNCTION sort(_int4)\r\n" + |
368 | "RETURNS _int4\r\n" + |
369 | "AS '$libdir/_int'\r\n" + |
370 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
371 | "\r\n" + |
372 | "CREATE OR REPLACE FUNCTION sort_asc(_int4)\r\n" + |
373 | "RETURNS _int4\r\n" + |
374 | "AS '$libdir/_int'\r\n" + |
375 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
376 | "\r\n" + |
377 | "CREATE OR REPLACE FUNCTION sort_desc(_int4)\r\n" + |
378 | "RETURNS _int4\r\n" + |
379 | "AS '$libdir/_int'\r\n" + |
380 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
381 | "\r\n" + |
382 | "CREATE OR REPLACE FUNCTION uniq(_int4)\r\n" + |
383 | "RETURNS _int4\r\n" + |
384 | "AS '$libdir/_int'\r\n" + |
385 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
386 | "\r\n" + |
387 | "CREATE OR REPLACE FUNCTION idx(_int4, int4)\r\n" + |
388 | "RETURNS int4\r\n" + |
389 | "AS '$libdir/_int'\r\n" + |
390 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
391 | "\r\n" + |
392 | "CREATE OPERATOR # (\r\n" + |
393 | " LEFTARG = _int4,\r\n" + |
394 | " RIGHTARG = int4,\r\n" + |
395 | " PROCEDURE = idx\r\n" + |
396 | ");\r\n" + |
397 | "\r\n" + |
398 | "CREATE OR REPLACE FUNCTION subarray(_int4, int4, int4)\r\n" + |
399 | "RETURNS _int4\r\n" + |
400 | "AS '$libdir/_int'\r\n" + |
401 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
402 | "\r\n" + |
403 | "CREATE OR REPLACE FUNCTION subarray(_int4, int4)\r\n" + |
404 | "RETURNS _int4\r\n" + |
405 | "AS '$libdir/_int'\r\n" + |
406 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
407 | "\r\n" + |
408 | "CREATE OR REPLACE FUNCTION intarray_push_elem(_int4, int4)\r\n" + |
409 | "RETURNS _int4\r\n" + |
410 | "AS '$libdir/_int'\r\n" + |
411 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
412 | "\r\n" + |
413 | "CREATE OPERATOR + (\r\n" + |
414 | " LEFTARG = _int4,\r\n" + |
415 | " RIGHTARG = int4,\r\n" + |
416 | " PROCEDURE = intarray_push_elem\r\n" + |
417 | ");\r\n" + |
418 | "\r\n" + |
419 | "CREATE OR REPLACE FUNCTION intarray_push_array(_int4, _int4)\r\n" + |
420 | "RETURNS _int4\r\n" + |
421 | "AS '$libdir/_int'\r\n" + |
422 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
423 | "\r\n" + |
424 | "CREATE OPERATOR + (\r\n" + |
425 | " LEFTARG = _int4,\r\n" + |
426 | " RIGHTARG = _int4,\r\n" + |
427 | " COMMUTATOR = +,\r\n" + |
428 | " PROCEDURE = intarray_push_array\r\n" + |
429 | ");\r\n" + |
430 | "\r\n" + |
431 | "CREATE OR REPLACE FUNCTION intarray_del_elem(_int4, int4)\r\n" + |
432 | "RETURNS _int4\r\n" + |
433 | "AS '$libdir/_int'\r\n" + |
434 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
435 | "\r\n" + |
436 | "CREATE OPERATOR - (\r\n" + |
437 | " LEFTARG = _int4,\r\n" + |
438 | " RIGHTARG = int4,\r\n" + |
439 | " PROCEDURE = intarray_del_elem\r\n" + |
440 | ");\r\n" + |
441 | "\r\n" + |
442 | "CREATE OR REPLACE FUNCTION intset_union_elem(_int4, int4)\r\n" + |
443 | "RETURNS _int4\r\n" + |
444 | "AS '$libdir/_int'\r\n" + |
445 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
446 | "\r\n" + |
447 | "CREATE OPERATOR | (\r\n" + |
448 | " LEFTARG = _int4,\r\n" + |
449 | " RIGHTARG = int4,\r\n" + |
450 | " PROCEDURE = intset_union_elem\r\n" + |
451 | ");\r\n" + |
452 | "\r\n" + |
453 | "CREATE OPERATOR | (\r\n" + |
454 | " LEFTARG = _int4,\r\n" + |
455 | " RIGHTARG = _int4,\r\n" + |
456 | " COMMUTATOR = |,\r\n" + |
457 | " PROCEDURE = _int_union\r\n" + |
458 | ");\r\n" + |
459 | "\r\n" + |
460 | "CREATE OR REPLACE FUNCTION intset_subtract(_int4, _int4)\r\n" + |
461 | "RETURNS _int4\r\n" + |
462 | "AS '$libdir/_int'\r\n" + |
463 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
464 | "\r\n" + |
465 | "CREATE OPERATOR - (\r\n" + |
466 | " LEFTARG = _int4,\r\n" + |
467 | " RIGHTARG = _int4,\r\n" + |
468 | " PROCEDURE = intset_subtract\r\n" + |
469 | ");\r\n" + |
470 | "\r\n" + |
471 | "CREATE OPERATOR & (\r\n" + |
472 | " LEFTARG = _int4,\r\n" + |
473 | " RIGHTARG = _int4,\r\n" + |
474 | " COMMUTATOR = &,\r\n" + |
475 | " PROCEDURE = _int_inter\r\n" + |
476 | ");\r\n" + |
477 | "--------------\r\n" + |
478 | "\r\n" + |
479 | "-- define the GiST support methods\r\n" + |
480 | "CREATE OR REPLACE FUNCTION g_int_consistent(internal,_int4,int,oid,internal)\r\n" + |
481 | "RETURNS bool\r\n" + |
482 | "AS '$libdir/_int'\r\n" + |
483 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
484 | "\r\n" + |
485 | "CREATE OR REPLACE FUNCTION g_int_compress(internal)\r\n" + |
486 | "RETURNS internal\r\n" + |
487 | "AS '$libdir/_int'\r\n" + |
488 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
489 | "\r\n" + |
490 | "CREATE OR REPLACE FUNCTION g_int_decompress(internal)\r\n" + |
491 | "RETURNS internal\r\n" + |
492 | "AS '$libdir/_int'\r\n" + |
493 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
494 | "\r\n" + |
495 | "CREATE OR REPLACE FUNCTION g_int_penalty(internal,internal,internal)\r\n" + |
496 | "RETURNS internal\r\n" + |
497 | "AS '$libdir/_int'\r\n" + |
498 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
499 | "\r\n" + |
500 | "CREATE OR REPLACE FUNCTION g_int_picksplit(internal, internal)\r\n" + |
501 | "RETURNS internal\r\n" + |
502 | "AS '$libdir/_int'\r\n" + |
503 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
504 | "\r\n" + |
505 | "CREATE OR REPLACE FUNCTION g_int_union(internal, internal)\r\n" + |
506 | "RETURNS _int4\r\n" + |
507 | "AS '$libdir/_int'\r\n" + |
508 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
509 | "\r\n" + |
510 | "CREATE OR REPLACE FUNCTION g_int_same(_int4, _int4, internal)\r\n" + |
511 | "RETURNS internal\r\n" + |
512 | "AS '$libdir/_int'\r\n" + |
513 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
514 | "\r\n" + |
515 | "\r\n" + |
516 | "-- Create the operator class for indexing\r\n" + |
517 | "\r\n" + |
518 | "\r\n" + |
519 | "---------------------------------------------\r\n" + |
520 | "-- intbig\r\n" + |
521 | "---------------------------------------------\r\n" + |
522 | "-- define the GiST support methods\r\n" + |
523 | "\r\n" + |
524 | "CREATE OR REPLACE FUNCTION _intbig_in(cstring)\r\n" + |
525 | "RETURNS intbig_gkey\r\n" + |
526 | "AS '$libdir/_int'\r\n" + |
527 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
528 | "\r\n" + |
529 | "CREATE OR REPLACE FUNCTION _intbig_out(intbig_gkey)\r\n" + |
530 | "RETURNS cstring\r\n" + |
531 | "AS '$libdir/_int'\r\n" + |
532 | "LANGUAGE C STRICT IMMUTABLE;\r\n" + |
533 | "\r\n" + |
534 | "CREATE TYPE intbig_gkey (\r\n" + |
535 | " INTERNALLENGTH = -1,\r\n" + |
536 | " INPUT = _intbig_in,\r\n" + |
537 | " OUTPUT = _intbig_out\r\n" + |
538 | ");\r\n" + |
539 | "\r\n" + |
540 | "CREATE OR REPLACE FUNCTION g_intbig_consistent(internal,internal,int,oid,internal)\r\n" + |
541 | "RETURNS bool\r\n" + |
542 | "AS '$libdir/_int'\r\n" + |
543 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
544 | "\r\n" + |
545 | "CREATE OR REPLACE FUNCTION g_intbig_compress(internal)\r\n" + |
546 | "RETURNS internal\r\n" + |
547 | "AS '$libdir/_int'\r\n" + |
548 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
549 | "\r\n" + |
550 | "CREATE OR REPLACE FUNCTION g_intbig_decompress(internal)\r\n" + |
551 | "RETURNS internal\r\n" + |
552 | "AS '$libdir/_int'\r\n" + |
553 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
554 | "\r\n" + |
555 | "CREATE OR REPLACE FUNCTION g_intbig_penalty(internal,internal,internal)\r\n" + |
556 | "RETURNS internal\r\n" + |
557 | "AS '$libdir/_int'\r\n" + |
558 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
559 | "\r\n" + |
560 | "CREATE OR REPLACE FUNCTION g_intbig_picksplit(internal, internal)\r\n" + |
561 | "RETURNS internal\r\n" + |
562 | "AS '$libdir/_int'\r\n" + |
563 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
564 | "\r\n" + |
565 | "CREATE OR REPLACE FUNCTION g_intbig_union(internal, internal)\r\n" + |
566 | "RETURNS _int4\r\n" + |
567 | "AS '$libdir/_int'\r\n" + |
568 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
569 | "\r\n" + |
570 | "CREATE OR REPLACE FUNCTION g_intbig_same(internal, internal, internal)\r\n" + |
571 | "RETURNS internal\r\n" + |
572 | "AS '$libdir/_int'\r\n" + |
573 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
574 | "\r\n" + |
575 | "-- register the opclass for indexing (not as default)\r\n" + |
576 | "\r\n" + |
577 | "CREATE OPERATOR CLASS gist__intbig_ops\r\n" + |
578 | "FOR TYPE _int4 USING gist\r\n" + |
579 | "AS\r\n" + |
580 | " OPERATOR 3 &&,\r\n" + |
581 | " OPERATOR 6 = (anyarray, anyarray),\r\n" + |
582 | " OPERATOR 7 @>,\r\n" + |
583 | " OPERATOR 8 <@,\r\n" + |
584 | " OPERATOR 13 @,\r\n" + |
585 | " OPERATOR 14 ~,\r\n" + |
586 | " OPERATOR 20 @@ (_int4, query_int),\r\n" + |
587 | " FUNCTION 1 g_intbig_consistent (internal, internal, int, oid, internal),\r\n" + |
588 | " FUNCTION 2 g_intbig_union (internal, internal),\r\n" + |
589 | " FUNCTION 3 g_intbig_compress (internal),\r\n" + |
590 | " FUNCTION 4 g_intbig_decompress (internal),\r\n" + |
591 | " FUNCTION 5 g_intbig_penalty (internal, internal, internal),\r\n" + |
592 | " FUNCTION 6 g_intbig_picksplit (internal, internal),\r\n" + |
593 | " FUNCTION 7 g_intbig_same (internal, internal, internal),\r\n" + |
594 | " STORAGE intbig_gkey;\r\n" + |
595 | "\r\n" + |
596 | "--GIN\r\n" + |
597 | "\r\n" + |
598 | "CREATE OR REPLACE FUNCTION ginint4_queryextract(internal, internal, int2, internal, internal)\r\n" + |
599 | "RETURNS internal\r\n" + |
600 | "AS '$libdir/_int'\r\n" + |
601 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
602 | "\r\n" + |
603 | "CREATE OR REPLACE FUNCTION ginint4_consistent(internal, int2, internal, int4, internal, internal)\r\n" + |
604 | "RETURNS bool\r\n" + |
605 | "AS '$libdir/_int'\r\n" + |
606 | "LANGUAGE C IMMUTABLE STRICT;\r\n" + |
607 | "\r\n" + |
608 | "CREATE OPERATOR CLASS gin__int_ops\r\n" + |
609 | "FOR TYPE _int4 USING gin\r\n" + |
610 | "AS\r\n" + |
611 | " OPERATOR 3 &&,\r\n" + |
612 | " OPERATOR 6 = (anyarray, anyarray),\r\n" + |
613 | " OPERATOR 7 @>,\r\n" + |
614 | " OPERATOR 8 <@,\r\n" + |
615 | " OPERATOR 13 @,\r\n" + |
616 | " OPERATOR 14 ~,\r\n" + |
617 | " OPERATOR 20 @@ (_int4, query_int),\r\n" + |
618 | " FUNCTION 1 btint4cmp (int4, int4),\r\n" + |
619 | " FUNCTION 2 ginarrayextract (anyarray, internal),\r\n" + |
620 | " FUNCTION 3 ginint4_queryextract (internal, internal, int2, internal, internal),\r\n" + |
621 | " FUNCTION 4 ginint4_consistent (internal, int2, internal, int4, internal, internal),\r\n" + |
622 | " STORAGE int4;\r\n" + |
623 | ""; |
624 | |
625 | } |