1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
-- operators
select a, b,
a <= b as "<=", a < b as "<", a = b as "=", a <> b as "<>", a >= b as ">=", a > b as ">",
a @> b as "@>", a <@ b as "<@", a && b as "&&"
from (select a::prefix_range, b::prefix_range
from (values('123', '123'),
('123', '124'),
('123', '123[4-5]'),
('123[4-5]', '123[2-7]'),
('123', '[2-3]')) as t(a, b)
) as x;
a | b | <= | < | = | <> | >= | > | @> | <@ | &&
----------+----------+----+---+---+----+----+---+----+----+----
123 | 123 | t | f | t | f | t | f | t | t | t
123 | 124 | t | t | f | t | f | f | f | f | f
123 | 123[4-5] | t | t | f | t | f | f | t | f | t
123[4-5] | 123[2-7] | f | f | f | t | t | t | f | t | t
123 | [2-3] | t | t | f | t | f | f | f | f | f
(5 rows)
-- transitivity
select a, b, c, a <= b as "a <= b", b <= c as "b <= c", a <= c as "a <= c"
from (select a::prefix_range, b::prefix_range, c::prefix_range
from (values('123', '123', '123'),
('123', '124', '125'),
('123', '123[4-5]', '123[4-6]'),
('123[4-5]', '123[2-7]', '123[1-8]'),
('123', '[2-3]', '4')) as t(a, b, c)
) as x;
a | b | c | a <= b | b <= c | a <= c
----------+----------+----------+--------+--------+--------
123 | 123 | 123 | t | t | t
123 | 124 | 125 | t | t | t
123 | 123[4-5] | 123[4-6] | t | t | t
123[4-5] | 123[2-7] | 123[1-8] | f | f | f
123 | [2-3] | 4 | t | t | t
(5 rows)
-- set operations
select a, b, a | b as union, a & b as intersect
from (select a::prefix_range, b::prefix_range
from (values('123', '123'),
('123', '124'),
('123', '123[4-5]'),
('123[4-5]', '123[2-7]'),
('123', '[2-3]')) as t(a, b)
) as x;
a | b | union | intersect
----------+----------+----------+-----------
123 | 123 | 123 | 123
123 | 124 | 12[3-4] |
123 | 123[4-5] | 123 | 123[4-5]
123[4-5] | 123[2-7] | 123[2-7] | 123[4-7]
123 | [2-3] | [1-3] |
(5 rows)
-- casting to and from text
select prefix_range('123');
prefix_range
--------------
123
(1 row)
select prefix_range('123[4-5]');
prefix_range
--------------
123[4-5]
(1 row)
select prefix_range('[2-3]');
prefix_range
--------------
[2-3]
(1 row)
select prefix_range('123', '4', '5');
prefix_range
--------------
123[4-5]
(1 row)
select length('12345'::prefix_range);
length
--------
5
(1 row)
select length('12345[]'::prefix_range);
length
--------
5
(1 row)
select length('123[4-5]'::prefix_range);
length
--------
5
(1 row)
select length('1234'::prefix_range);
length
--------
4
(1 row)
\dC *prefix*
List of casts
Source type | Target type | Function | Implicit?
--------------+--------------+--------------+-----------
prefix_range | text | text | no
text | prefix_range | prefix_range | yes
(2 rows)
|
