I tried to define my own circle operator to use in an EXCLUDE constraint but it fails to detect
insertion of rows that should not be simultaneously be allowed in the table. The operator
compares two circles' radii and works for a simple SELECT. What am I doing wrong?
Here is the code to reproduce. The second insert at the end should fail because the two circles
have the same radius.
CREATE OR REPLACE FUNCTION circradcmp(aa CIRCLE, bb CIRCLE)
RETURNS BOOLEAN AS $$
DECLARE zz DOUBLE PRECISION;
BEGIN
zz := abs(radius(aa) - radius(bb));
IF (zz < 0.0005)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE OPERATOR === (
LEFTARG = CIRCLE,
RIGHTARG = CIRCLE,
PROCEDURE = circradcmp,
COMMUTATOR = ===
);
ALTER OPERATOR FAMILY circle_ops USING gist ADD
OPERATOR 15 === (circle, circle);
CREATE TABLE punky
(
acirc CIRCLE,
EXCLUDE USING GIST (acirc circle_ops WITH ===)
);
INSERT INTO punky VALUES ('(0,0),3)');
INSERT INTO punky VALUES ('(7,0),3)');
Paul Jones
