circle.out

来自「PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统」· OUT 代码 · 共 94 行

--
-- CIRCLE
--
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>');
-- bad values
INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>');
ERROR:  invalid input syntax for type circle: "<(-100,0),-100>"
INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5');
ERROR:  invalid input syntax for type circle: "1abc,3,5"
INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)');
ERROR:  invalid input syntax for type circle: "(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>
(6 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)
(6 rows)

SELECT '' AS six, radius(f1) AS radius
  FROM CIRCLE_TBL;
 six | radius 
-----+--------
     |      3
     |    100
     |      5
     |      3
     |     10
     |    115
(6 rows)

SELECT '' AS six, diameter(f1) AS diameter
  FROM CIRCLE_TBL;
 six | diameter 
-----+----------
     |        6
     |      200
     |       10
     |        6
     |       20
     |      230
(6 rows)

SELECT '' AS two, f1 FROM CIRCLE_TBL WHERE radius(f1) < 5;
 two |    f1     
-----+-----------
     | <(5,1),3>
     | <(1,2),3>
(2 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>
(4 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, one USING < , two USING < ;
 five |      one       |      two       |     distance     
------+----------------+----------------+------------------
      | <(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
      | <(1,2),3>      | <(100,200),10> | 208.370729772479
(5 rows)