From: | Paul Jones <pbj(at)cmicdo(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | User defined operator fails to work in EXCLUDE constraint |
Date: | 2014-04-13 20:29:11 |
Message-ID: | 1397420951.44351.YahooMailNeo@web161702.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-13 21:25:27 | Re: User defined operator fails to work in EXCLUDE constraint |
Previous Message | Moshe Jacobson | 2014-04-13 15:09:23 | Re: Database Design: Maintain Audit Trail of Changes |