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
114
115
116
117
118
119
120
121
122
123
124
|
--
-- CIRCLE
--
-- avoid bit-exact output here because operations may not be bit-exact.
SET extra_float_digits = 0;
CREATE TABLE CIRCLE_TBL (f1 circle);
INSERT INTO CIRCLE_TBL VALUES ('<(5,1),3>');
INSERT INTO CIRCLE_TBL VALUES ('<(1,2),100>');
INSERT INTO CIRCLE_TBL VALUES ('1,3,5');
INSERT INTO CIRCLE_TBL VALUES ('((1,2),3)');
INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10>');
INSERT INTO CIRCLE_TBL VALUES (' < ( 100 , 1 ) , 115 > ');
INSERT INTO CIRCLE_TBL VALUES ('<(3,5),0>'); -- Zero radius
INSERT INTO CIRCLE_TBL VALUES ('<(3,5),NaN>'); -- NaN radius
-- bad values
INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>');
ERROR: invalid input syntax for type circle: "<(-100,0),-100>"
LINE 1: INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>');
^
INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10');
ERROR: invalid input syntax for type circle: "<(100,200),10"
LINE 1: INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10');
^
INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10> x');
ERROR: invalid input syntax for type circle: "<(100,200),10> x"
LINE 1: INSERT INTO CIRCLE_TBL VALUES ('<(100,200),10> x');
^
INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5');
ERROR: invalid input syntax for type circle: "1abc,3,5"
LINE 1: INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5');
^
INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)');
ERROR: invalid input syntax for type circle: "(3,(1,2),3)"
LINE 1: INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)');
^
SELECT * FROM CIRCLE_TBL;
f1
----------------
<(5,1),3>
<(1,2),100>
<(1,3),5>
<(1,2),3>
<(100,200),10>
<(100,1),115>
<(3,5),0>
<(3,5),NaN>
(8 rows)
SELECT '' AS six, center(f1) AS center
FROM CIRCLE_TBL;
six | center
-----+-----------
| (5,1)
| (1,2)
| (1,3)
| (1,2)
| (100,200)
| (100,1)
| (3,5)
| (3,5)
(8 rows)
SELECT '' AS six, radius(f1) AS radius
FROM CIRCLE_TBL;
six | radius
-----+--------
| 3
| 100
| 5
| 3
| 10
| 115
| 0
| NaN
(8 rows)
SELECT '' AS six, diameter(f1) AS diameter
FROM CIRCLE_TBL;
six | diameter
-----+----------
| 6
| 200
| 10
| 6
| 20
| 230
| 0
| NaN
(8 rows)
SELECT '' AS two, f1 FROM CIRCLE_TBL WHERE radius(f1) < 5;
two | f1
-----+-----------
| <(5,1),3>
| <(1,2),3>
| <(3,5),0>
(3 rows)
SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10;
four | f1
------+----------------
| <(1,2),100>
| <(1,3),5>
| <(100,200),10>
| <(100,1),115>
| <(3,5),NaN>
(5 rows)
SELECT '' as five, c1.f1 AS one, c2.f1 AS two, (c1.f1 <-> c2.f1) AS distance
FROM CIRCLE_TBL c1, CIRCLE_TBL c2
WHERE (c1.f1 < c2.f1) AND ((c1.f1 <-> c2.f1) > 0)
ORDER BY distance, area(c1.f1), area(c2.f1);
five | one | two | distance
------+----------------+----------------+------------------
| <(3,5),0> | <(1,2),3> | 0.60555127546399
| <(3,5),0> | <(5,1),3> | 1.47213595499958
| <(100,200),10> | <(100,1),115> | 74
| <(100,200),10> | <(1,2),100> | 111.370729772479
| <(1,3),5> | <(100,200),10> | 205.476756144497
| <(5,1),3> | <(100,200),10> | 207.51303816328
| <(3,5),0> | <(100,200),10> | 207.793480159531
| <(1,2),3> | <(100,200),10> | 208.370729772479
(8 rows)
|
