User defined operator fails to work in EXCLUDE constraint

Поиск
Список
Период
Сортировка
От Paul Jones
Тема User defined operator fails to work in EXCLUDE constraint
Дата
Msg-id 1397420951.44351.YahooMailNeo@web161702.mail.bf1.yahoo.com
обсуждение исходный текст
Ответы Re: User defined operator fails to work in EXCLUDE constraint
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Moshe Jacobson
Дата:
Сообщение: Re: Database Design: Maintain Audit Trail of Changes
Следующее
От: Tom Lane
Дата:
Сообщение: Re: User defined operator fails to work in EXCLUDE constraint