Negator functions

A negator function takes the same arguments as its companion function, in the same order, but returns the Boolean complement. That is, if a function returns TRUE for a given set of arguments, its negator function returns FALSE when passed the same arguments, in the same order. In certain cases, the database server can process a query more efficiently if the sense of the query is reversed; that is, if the query is, “Is x greater than y?” instead of, “Is y less than or equal to x?”

The NEGATOR modifier of the CREATE FUNCTION statement names a companion function, a negator function, to the current function. When you provide a negator function, the optimizer can use a negator function instead of the function you specify when it is more efficient to do so. If a function has a negator function, any user who executes the function must have the Execute privilege on both the function and its negator. In addition, a function must have the same owner as its negator function.

CREATE ROW TYPE complex(real FLOAT, imag FLOAT);

CREATE FUNCTION equal (c1 complex, c2 complex)
   RETURNING BOOLEAN WITH (NEGATOR = notequal)
   DEFINE a BOOLEAN;
   IF (c1.real = c2.real) AND (c1.imag = c2.imag) THEN
      LET a = 't';
   ELSE
      LET a = 'f';
   END IF;
   RETURN a;
END FUNCTION;


CREATE FUNCTION notequal (c1 complex, c2 complex)
   RETURNING BOOLEAN WITH (NEGATOR = equal)
   DEFINE a BOOLEAN;
   IF (c1.real != c2.real) OR (c1.imag != c2.imag) THEN
      LET a = 't';
   ELSE
      LET a = 'f';
   END IF;
   RETURN a;
END FUNCTION;