circle.sql

关系型数据库 Postgresql 6.5.2

--
-- circles
--

CREATE TABLE CIRCLE_TBL (f1 circle);

INSERT INTO CIRCLE_TBL VALUES ('<(0,0),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,0),100>');

-- bad values

INSERT INTO CIRCLE_TBL VALUES ('<(-100,0),-100>');

INSERT INTO CIRCLE_TBL VALUES ('1abc,3,5');

INSERT INTO CIRCLE_TBL VALUES ('(3,(1,2),3)');

SELECT * FROM CIRCLE_TBL;

SELECT '' AS six, center(f1) AS center
  FROM CIRCLE_TBL;

SELECT '' AS six, radius(f1) AS radius
  FROM CIRCLE_TBL;

SELECT '' AS six, diameter(f1) AS diameter
  FROM CIRCLE_TBL;

SELECT '' AS two, f1 FROM CIRCLE_TBL WHERE radius(f1) < 5;

SELECT '' AS four, f1 FROM CIRCLE_TBL WHERE diameter(f1) >= 10;

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, two;