ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/bioseg/trunk/bioseg.sql.in
Revision: 20
Committed: Mon Apr 7 10:40:19 2008 UTC (11 years, 4 months ago) by kmr
File size: 10265 byte(s)
Log Message:
Documentation fix.

Line User Rev File contents
1 kmr 20 -- Create the user-defined type for integer intervals (bioseg)
2 kmr 1 --
3    
4     -- Adjust this setting to control where the objects get created.
5     SET search_path = public;
6    
7     CREATE FUNCTION $BIOSEG_TYPE_in(cstring)
8     RETURNS $BIOSEG_TYPE
9     AS '$libdir/$BIOSEG_TYPE'
10     LANGUAGE C STRICT IMMUTABLE;
11    
12     CREATE FUNCTION $BIOSEG_TYPE_out($BIOSEG_TYPE)
13     RETURNS cstring
14     AS '$libdir/$BIOSEG_TYPE'
15     LANGUAGE C STRICT IMMUTABLE;
16    
17     CREATE FUNCTION $BIOSEG_TYPE_create(int4, int4)
18     RETURNS $BIOSEG_TYPE
19     AS '$libdir/$BIOSEG_TYPE'
20     LANGUAGE C STRICT IMMUTABLE;
21    
22     CREATE TYPE $BIOSEG_TYPE (
23     INTERNALLENGTH = 8,
24     INPUT = $BIOSEG_TYPE_in,
25     OUTPUT = $BIOSEG_TYPE_out
26     );
27    
28     COMMENT ON TYPE $BIOSEG_TYPE IS
29 kmr 20 'integer point interval ''INT..INT'', ''INT...INT'', or ''INT''';
30 kmr 1
31     --
32     -- External C-functions for R-tree methods
33     --
34    
35     -- Left/Right methods
36    
37     CREATE FUNCTION $BIOSEG_TYPE_over_left($BIOSEG_TYPE, $BIOSEG_TYPE)
38     RETURNS bool
39     AS '$libdir/$BIOSEG_TYPE'
40     LANGUAGE C STRICT IMMUTABLE;
41    
42     COMMENT ON FUNCTION $BIOSEG_TYPE_over_left($BIOSEG_TYPE, $BIOSEG_TYPE) IS
43     'overlaps or is left of';
44    
45     CREATE FUNCTION $BIOSEG_TYPE_over_right($BIOSEG_TYPE, $BIOSEG_TYPE)
46     RETURNS bool
47     AS '$libdir/$BIOSEG_TYPE'
48     LANGUAGE C STRICT IMMUTABLE;
49    
50     COMMENT ON FUNCTION $BIOSEG_TYPE_over_right($BIOSEG_TYPE, $BIOSEG_TYPE) IS
51     'overlaps or is right of';
52    
53     CREATE FUNCTION $BIOSEG_TYPE_left($BIOSEG_TYPE, $BIOSEG_TYPE)
54     RETURNS bool
55     AS '$libdir/$BIOSEG_TYPE'
56     LANGUAGE C STRICT IMMUTABLE;
57    
58     COMMENT ON FUNCTION $BIOSEG_TYPE_left($BIOSEG_TYPE, $BIOSEG_TYPE) IS
59     'is left of';
60    
61     CREATE FUNCTION $BIOSEG_TYPE_right($BIOSEG_TYPE, $BIOSEG_TYPE)
62     RETURNS bool
63     AS '$libdir/$BIOSEG_TYPE'
64     LANGUAGE C STRICT IMMUTABLE;
65    
66     COMMENT ON FUNCTION $BIOSEG_TYPE_right($BIOSEG_TYPE, $BIOSEG_TYPE) IS
67     'is right of';
68    
69    
70     -- Scalar comparison methods
71    
72     CREATE FUNCTION $BIOSEG_TYPE_lt($BIOSEG_TYPE, $BIOSEG_TYPE)
73     RETURNS bool
74     AS '$libdir/$BIOSEG_TYPE'
75     LANGUAGE C STRICT IMMUTABLE;
76    
77     COMMENT ON FUNCTION $BIOSEG_TYPE_lt($BIOSEG_TYPE, $BIOSEG_TYPE) IS
78     'less than';
79    
80     CREATE FUNCTION $BIOSEG_TYPE_le($BIOSEG_TYPE, $BIOSEG_TYPE)
81     RETURNS bool
82     AS '$libdir/$BIOSEG_TYPE'
83     LANGUAGE C STRICT IMMUTABLE;
84    
85     COMMENT ON FUNCTION $BIOSEG_TYPE_le($BIOSEG_TYPE, $BIOSEG_TYPE) IS
86     'less than or equal';
87    
88     CREATE FUNCTION $BIOSEG_TYPE_gt($BIOSEG_TYPE, $BIOSEG_TYPE)
89     RETURNS bool
90     AS '$libdir/$BIOSEG_TYPE'
91     LANGUAGE C STRICT IMMUTABLE;
92    
93     COMMENT ON FUNCTION $BIOSEG_TYPE_gt($BIOSEG_TYPE, $BIOSEG_TYPE) IS
94     'greater than';
95    
96     CREATE FUNCTION $BIOSEG_TYPE_ge($BIOSEG_TYPE, $BIOSEG_TYPE)
97     RETURNS bool
98     AS '$libdir/$BIOSEG_TYPE'
99     LANGUAGE C STRICT IMMUTABLE;
100    
101     COMMENT ON FUNCTION $BIOSEG_TYPE_ge($BIOSEG_TYPE, $BIOSEG_TYPE) IS
102     'greater than or equal';
103    
104     CREATE FUNCTION $BIOSEG_TYPE_contains($BIOSEG_TYPE, $BIOSEG_TYPE)
105     RETURNS bool
106     AS '$libdir/$BIOSEG_TYPE'
107     LANGUAGE C STRICT IMMUTABLE;
108    
109     COMMENT ON FUNCTION $BIOSEG_TYPE_contains($BIOSEG_TYPE, $BIOSEG_TYPE) IS
110     'contains';
111    
112     CREATE FUNCTION $BIOSEG_TYPE_contained($BIOSEG_TYPE, $BIOSEG_TYPE)
113     RETURNS bool
114     AS '$libdir/$BIOSEG_TYPE'
115     LANGUAGE C STRICT IMMUTABLE;
116    
117     COMMENT ON FUNCTION $BIOSEG_TYPE_contained($BIOSEG_TYPE, $BIOSEG_TYPE) IS
118     'contained in';
119    
120     CREATE FUNCTION $BIOSEG_TYPE_overlap($BIOSEG_TYPE, $BIOSEG_TYPE)
121     RETURNS bool
122     AS '$libdir/$BIOSEG_TYPE'
123     LANGUAGE C STRICT IMMUTABLE;
124    
125     COMMENT ON FUNCTION $BIOSEG_TYPE_overlap($BIOSEG_TYPE, $BIOSEG_TYPE) IS
126     'overlaps';
127    
128     CREATE FUNCTION $BIOSEG_TYPE_same($BIOSEG_TYPE, $BIOSEG_TYPE)
129     RETURNS bool
130     AS '$libdir/$BIOSEG_TYPE'
131     LANGUAGE C STRICT IMMUTABLE;
132    
133     COMMENT ON FUNCTION $BIOSEG_TYPE_same($BIOSEG_TYPE, $BIOSEG_TYPE) IS
134     'same as';
135    
136     CREATE FUNCTION $BIOSEG_TYPE_different($BIOSEG_TYPE, $BIOSEG_TYPE)
137     RETURNS bool
138     AS '$libdir/$BIOSEG_TYPE'
139     LANGUAGE C STRICT IMMUTABLE;
140    
141     COMMENT ON FUNCTION $BIOSEG_TYPE_different($BIOSEG_TYPE, $BIOSEG_TYPE) IS
142     'different';
143    
144     -- support routines for indexing
145    
146     CREATE OR REPLACE FUNCTION $BIOSEG_TYPE_cmp($BIOSEG_TYPE, $BIOSEG_TYPE)
147     RETURNS int4
148     AS '$libdir/$BIOSEG_TYPE'
149     LANGUAGE C STRICT IMMUTABLE;
150    
151     COMMENT ON FUNCTION $BIOSEG_TYPE_cmp($BIOSEG_TYPE, $BIOSEG_TYPE) IS 'btree comparison function';
152    
153     CREATE FUNCTION $BIOSEG_TYPE_size($BIOSEG_TYPE)
154     RETURNS int4
155     AS '$libdir/$BIOSEG_TYPE'
156     LANGUAGE C STRICT IMMUTABLE;
157    
158     -- miscellaneous
159    
160     CREATE FUNCTION $BIOSEG_TYPE_upper($BIOSEG_TYPE)
161     RETURNS int4
162     AS '$libdir/$BIOSEG_TYPE'
163     LANGUAGE C STRICT IMMUTABLE;
164    
165     CREATE FUNCTION $BIOSEG_TYPE_lower($BIOSEG_TYPE)
166     RETURNS int4
167     AS '$libdir/$BIOSEG_TYPE'
168     LANGUAGE C STRICT IMMUTABLE;
169    
170     CREATE FUNCTION $BIOSEG_TYPE_sel(internal, oid, internal, integer)
171     RETURNS float
172     AS '$libdir/$BIOSEG_TYPE'
173     LANGUAGE C STRICT IMMUTABLE;
174    
175     CREATE FUNCTION $BIOSEG_TYPE_joinsel(internal, oid, internal, smallint)
176     RETURNS FLOAT
177     AS '$libdir/$BIOSEG_TYPE'
178     LANGUAGE C STRICT IMMUTABLE;
179    
180     CREATE FUNCTION $BIOSEG_TYPE_contsel(internal, oid, internal, integer)
181     RETURNS float
182     AS '$libdir/$BIOSEG_TYPE'
183     LANGUAGE C STRICT IMMUTABLE;
184    
185     CREATE FUNCTION $BIOSEG_TYPE_contjoinsel(internal, oid, internal, smallint)
186     RETURNS FLOAT
187     AS '$libdir/$BIOSEG_TYPE'
188     LANGUAGE C STRICT IMMUTABLE;
189    
190    
191     --
192     -- OPERATORS
193     --
194    
195     CREATE OPERATOR < (
196     LEFTARG = $BIOSEG_TYPE,
197     RIGHTARG = $BIOSEG_TYPE,
198     PROCEDURE = $BIOSEG_TYPE_lt,
199     COMMUTATOR = '>',
200     NEGATOR = '>=',
201     RESTRICT = scalarltsel,
202     JOIN = scalarltjoinsel
203     );
204    
205     CREATE OPERATOR <= (
206     LEFTARG = $BIOSEG_TYPE,
207     RIGHTARG = $BIOSEG_TYPE,
208     PROCEDURE = $BIOSEG_TYPE_le,
209     COMMUTATOR = '>=',
210     NEGATOR = '>',
211     RESTRICT = scalarltsel,
212     JOIN = scalarltjoinsel
213     );
214    
215     CREATE OPERATOR > (
216     LEFTARG = $BIOSEG_TYPE,
217     RIGHTARG = $BIOSEG_TYPE,
218     PROCEDURE = $BIOSEG_TYPE_gt,
219     COMMUTATOR = '<',
220     NEGATOR = '<=',
221     RESTRICT = scalargtsel,
222     JOIN = scalargtjoinsel
223     );
224    
225     CREATE OPERATOR >= (
226     LEFTARG = $BIOSEG_TYPE,
227     RIGHTARG = $BIOSEG_TYPE,
228     PROCEDURE = $BIOSEG_TYPE_ge,
229     COMMUTATOR = '<=',
230     NEGATOR = '<',
231     RESTRICT = scalargtsel,
232     JOIN = scalargtjoinsel
233     );
234    
235     CREATE OPERATOR << (
236     LEFTARG = $BIOSEG_TYPE,
237     RIGHTARG = $BIOSEG_TYPE,
238     PROCEDURE = $BIOSEG_TYPE_left,
239     COMMUTATOR = '>>',
240     RESTRICT = positionsel,
241     JOIN = positionjoinsel
242     );
243    
244     CREATE OPERATOR &< (
245     LEFTARG = $BIOSEG_TYPE,
246     RIGHTARG = $BIOSEG_TYPE,
247     PROCEDURE = $BIOSEG_TYPE_over_left,
248     RESTRICT = positionsel,
249     JOIN = positionjoinsel
250     );
251    
252     CREATE OPERATOR && (
253     LEFTARG = $BIOSEG_TYPE,
254     RIGHTARG = $BIOSEG_TYPE,
255     PROCEDURE = $BIOSEG_TYPE_overlap,
256     COMMUTATOR = '&&',
257     RESTRICT = $BIOSEG_TYPE_sel,
258     JOIN = $BIOSEG_TYPE_joinsel
259     );
260    
261     CREATE OPERATOR &> (
262     LEFTARG = $BIOSEG_TYPE,
263     RIGHTARG = $BIOSEG_TYPE,
264     PROCEDURE = $BIOSEG_TYPE_over_right,
265     RESTRICT = positionsel,
266     JOIN = positionjoinsel
267     );
268    
269     CREATE OPERATOR >> (
270     LEFTARG = $BIOSEG_TYPE,
271     RIGHTARG = $BIOSEG_TYPE,
272     PROCEDURE = $BIOSEG_TYPE_right,
273     COMMUTATOR = '<<',
274     RESTRICT = positionsel,
275     JOIN = positionjoinsel
276     );
277    
278     CREATE OPERATOR = (
279     LEFTARG = $BIOSEG_TYPE,
280     RIGHTARG = $BIOSEG_TYPE,
281     PROCEDURE = $BIOSEG_TYPE_same,
282     COMMUTATOR = '=',
283     NEGATOR = '<>',
284     RESTRICT = eqsel,
285     JOIN = eqjoinsel,
286     MERGES
287     );
288    
289     CREATE OPERATOR <> (
290     LEFTARG = $BIOSEG_TYPE,
291     RIGHTARG = $BIOSEG_TYPE,
292     PROCEDURE = $BIOSEG_TYPE_different,
293     COMMUTATOR = '<>',
294     NEGATOR = '=',
295     RESTRICT = neqsel,
296     JOIN = neqjoinsel
297     );
298    
299     CREATE OPERATOR @> (
300     LEFTARG = $BIOSEG_TYPE,
301     RIGHTARG = $BIOSEG_TYPE,
302     PROCEDURE = $BIOSEG_TYPE_contains,
303     COMMUTATOR = '<@',
304     RESTRICT = $BIOSEG_TYPE_contsel,
305     JOIN = $BIOSEG_TYPE_contjoinsel
306     );
307    
308     CREATE OPERATOR <@ (
309     LEFTARG = $BIOSEG_TYPE,
310     RIGHTARG = $BIOSEG_TYPE,
311     PROCEDURE = $BIOSEG_TYPE_contained,
312     COMMUTATOR = '@>',
313     RESTRICT = $BIOSEG_TYPE_contsel,
314     JOIN = $BIOSEG_TYPE_contjoinsel
315     );
316    
317    
318    
319     -- define the GiST support methods
320     CREATE FUNCTION $BIOSEG_TYPE_gist_consistent(internal,$BIOSEG_TYPE,int4)
321     RETURNS bool
322     AS '$libdir/$BIOSEG_TYPE'
323 kmr 11 LANGUAGE C IMMUTABLE;
324 kmr 1
325     CREATE FUNCTION $BIOSEG_TYPE_gist_compress(internal)
326     RETURNS internal
327     AS '$libdir/$BIOSEG_TYPE'
328 kmr 11 LANGUAGE C IMMUTABLE;
329 kmr 1
330     CREATE FUNCTION $BIOSEG_TYPE_gist_decompress(internal)
331     RETURNS internal
332     AS '$libdir/$BIOSEG_TYPE'
333 kmr 11 LANGUAGE C IMMUTABLE;
334 kmr 1
335     CREATE FUNCTION $BIOSEG_TYPE_gist_penalty(internal,internal,internal)
336     RETURNS internal
337     AS '$libdir/$BIOSEG_TYPE'
338 kmr 11 LANGUAGE C STRICT IMMUTABLE;
339 kmr 1
340     CREATE FUNCTION $BIOSEG_TYPE_gist_picksplit(internal, internal)
341     RETURNS internal
342     AS '$libdir/$BIOSEG_TYPE'
343 kmr 11 LANGUAGE C IMMUTABLE;
344 kmr 1
345     CREATE FUNCTION $BIOSEG_TYPE_gist_union(internal, internal)
346     RETURNS $BIOSEG_TYPE
347     AS '$libdir/$BIOSEG_TYPE'
348 kmr 11 LANGUAGE C IMMUTABLE;
349 kmr 1
350     CREATE FUNCTION $BIOSEG_TYPE_gist_same($BIOSEG_TYPE, $BIOSEG_TYPE, internal)
351     RETURNS internal
352     AS '$libdir/$BIOSEG_TYPE'
353 kmr 11 LANGUAGE C IMMUTABLE;
354 kmr 1
355    
356     -- Create the operator classes for indexing
357    
358     CREATE OPERATOR CLASS $BIOSEG_TYPE_ops
359     DEFAULT FOR TYPE $BIOSEG_TYPE USING btree AS
360     OPERATOR 1 < ,
361     OPERATOR 2 <= ,
362     OPERATOR 3 = ,
363     OPERATOR 4 >= ,
364     OPERATOR 5 > ,
365     FUNCTION 1 $BIOSEG_TYPE_cmp($BIOSEG_TYPE, $BIOSEG_TYPE);
366    
367     CREATE OPERATOR CLASS gist_$BIOSEG_TYPE_ops
368     DEFAULT FOR TYPE $BIOSEG_TYPE USING gist
369     AS
370     OPERATOR 1 << ,
371     OPERATOR 2 &< ,
372     OPERATOR 3 && ,
373     OPERATOR 4 &> ,
374     OPERATOR 5 >> ,
375     OPERATOR 6 = ,
376     OPERATOR 7 @> ,
377     OPERATOR 8 <@ ,
378     FUNCTION 1 $BIOSEG_TYPE_gist_consistent (internal, $BIOSEG_TYPE, int4),
379     FUNCTION 2 $BIOSEG_TYPE_gist_union (internal, internal),
380     FUNCTION 3 $BIOSEG_TYPE_gist_compress (internal),
381     FUNCTION 4 $BIOSEG_TYPE_gist_decompress (internal),
382     FUNCTION 5 $BIOSEG_TYPE_gist_penalty (internal, internal, internal),
383     FUNCTION 6 $BIOSEG_TYPE_gist_picksplit (internal, internal),
384     FUNCTION 7 $BIOSEG_TYPE_gist_same ($BIOSEG_TYPE, $BIOSEG_TYPE, internal);