ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/bioseg/trunk/sql/bioseg.sql
Revision: 3
Committed: Tue Aug 14 23:05:57 2007 UTC (12 years, 1 month ago) by kmr
File size: 4081 byte(s)
Log Message:
Moved all files to trunk directory.

Line File contents
1 --
2 -- Test bioseg datatype
3 --
4
5 --
6 -- first, define the datatype. Turn off echoing so that expected file
7 -- does not depend on contents of bioseg.sql.
8 --
9 SET client_min_messages = warning;
10 \set ECHO none
11 \i bioseg.sql
12 \set ECHO all
13 RESET client_min_messages;
14
15 --
16 -- testing the input and output functions
17 --
18
19 -- Any number
20 SELECT '1'::bioseg AS bioseg;
21 SELECT '99999999'::bioseg AS bioseg;
22
23
24 -- Finite intervals
25 SELECT '1..2'::bioseg AS bioseg;
26 SELECT '10000000..20000000'::bioseg AS bioseg;
27
28 -- invalid input
29 SELECT ''::bioseg AS bioseg;
30 SELECT '..10'::bioseg AS bioseg;
31 SELECT '0..10'::bioseg AS bioseg;
32 SELECT '0'::bioseg AS bioseg;
33 SELECT '-10'::bioseg AS bioseg;
34 SELECT '-10..-1'::bioseg AS bioseg;
35 SELECT '-10..0'::bioseg AS bioseg;
36 SELECT '-10..1'::bioseg AS bioseg;
37 SELECT 'ABC'::bioseg AS bioseg;
38 SELECT '1ABC'::bioseg AS bioseg;
39 SELECT '1.'::bioseg AS bioseg;
40 SELECT '1.....'::bioseg AS bioseg;
41 SELECT '.1'::bioseg AS bioseg;
42 SELECT '1..2.'::bioseg AS bioseg;
43 SELECT '1 e7'::bioseg AS bioseg;
44 SELECT '1e700'::bioseg AS bioseg;
45
46 --
47 -- testing the operators
48 --
49
50 -- equality/inequality:
51 --
52 SELECT '24..33'::bioseg = '24..33'::bioseg AS bool;
53 SELECT '24..50'::bioseg != '24..33'::bioseg AS bool;
54
55 -- overlap
56 --
57 SELECT '2'::bioseg && '2'::bioseg AS bool;
58 SELECT '2'::bioseg && '2'::bioseg AS bool;
59 SELECT '2..2'::bioseg && '2..2'::bioseg AS bool;
60 SELECT '2..2'::bioseg && '2'::bioseg AS bool;
61 SELECT '2..2'::bioseg && '2'::bioseg AS bool;
62 SELECT '2..2'::bioseg && '2'::bioseg AS bool;
63 SELECT '2'::bioseg && '2..2'::bioseg AS bool;
64 SELECT '2'::bioseg && '2..2'::bioseg AS bool;
65 SELECT '2'::bioseg && '2..3'::bioseg AS bool;
66
67 -- overlap on the left
68 --
69 SELECT '2'::bioseg &< '2'::bioseg AS bool;
70 SELECT '2'::bioseg &< '3'::bioseg AS bool;
71 SELECT '1..2'::bioseg &< '2'::bioseg AS bool;
72 SELECT '1..2'::bioseg &< '3'::bioseg AS bool;
73 SELECT '1..2'::bioseg &< '1..2'::bioseg AS bool;
74 SELECT '1..2'::bioseg &< '1..3'::bioseg AS bool;
75 SELECT '1..2'::bioseg &< '2..3'::bioseg AS bool;
76 SELECT '1..2'::bioseg &< '3..4'::bioseg AS bool;
77
78 -- overlap on the right
79 --
80 SELECT '2'::bioseg &> '2'::bioseg AS bool;
81 SELECT '3'::bioseg &> '2'::bioseg AS bool;
82 SELECT '2'::bioseg &> '1..2'::bioseg AS bool;
83 SELECT '3'::bioseg &> '1..2'::bioseg AS bool;
84 SELECT '1..2'::bioseg &> '1..2'::bioseg AS bool;
85 SELECT '1..3'::bioseg &> '1..3'::bioseg AS bool;
86 SELECT '2..3'::bioseg &> '1..2'::bioseg AS bool;
87 SELECT '3..4'::bioseg &> '1..2'::bioseg AS bool;
88
89 -- left
90 --
91 SELECT '2'::bioseg << '2'::bioseg AS bool;
92 SELECT '2'::bioseg << '3'::bioseg AS bool;
93 SELECT '2..2'::bioseg << '2'::bioseg AS bool;
94 SELECT '2..3'::bioseg << '3'::bioseg AS bool;
95 SELECT '2..3'::bioseg << '3'::bioseg AS bool;
96 SELECT '2..3'::bioseg << '2..3'::bioseg AS bool;
97 SELECT '2..3'::bioseg << '2..4'::bioseg AS bool;
98 SELECT '2..3'::bioseg << '3..4'::bioseg AS bool;
99 SELECT '2..3'::bioseg << '3..5'::bioseg AS bool;
100
101 -- right
102 --
103 SELECT '2'::bioseg >> '2'::bioseg AS bool;
104 SELECT '3'::bioseg >> '2'::bioseg AS bool;
105 SELECT '2'::bioseg >> '2..2'::bioseg AS bool;
106 SELECT '3'::bioseg >> '2..3'::bioseg AS bool;
107 SELECT '2..3'::bioseg >> '2..3'::bioseg AS bool;
108 SELECT '3..4'::bioseg >> '2..3'::bioseg AS bool;
109
110
111 -- "contained in" (the left value belongs within the interval specified in the right value):
112 --
113 SELECT '2'::bioseg <@ '2'::bioseg AS bool;
114 SELECT '2'::bioseg <@ '2..3'::bioseg AS bool;
115 SELECT '3'::bioseg <@ '2..3'::bioseg AS bool;
116 SELECT '3..4'::bioseg <@ '2..5'::bioseg AS bool;
117 SELECT '2..5'::bioseg <@ '2..5'::bioseg AS bool;
118
119 -- "contains" (the left value contains the interval specified in the right value):
120 --
121 SELECT '2'::bioseg @> '2'::bioseg AS bool;
122 SELECT '2..4'::bioseg <@ '3'::bioseg AS bool;
123 SELECT '3'::bioseg <@ '2..4'::bioseg AS bool;
124
125 -- Load some example data and build the index
126 --
127 CREATE TABLE test_bioseg (s bioseg);
128
129 \copy test_bioseg from 'data/test_bioseg.data'
130
131 CREATE INDEX test_bioseg_ix ON test_bioseg USING gist (s);
132 SELECT count(*) FROM test_bioseg WHERE s @> '11..11';
133
134 -- Test sorting
135 SELECT * FROM test_bioseg WHERE s @> '11..20' GROUP BY s;
136
137 DROP TABLE test_bioseg;