很容易创建一个与NOT DISTINCT TO
相对应的函数和运算符:
CREATE FUNCTION ndist(anyelement, anyelement) RETURNS boolean
IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
AS 'SELECT $1 IS NOT DISTINCT FROM $2';
CREATE OPERATOR === (
PROCEDURE = ndist,
LEFTARG = anyelement,
RIGHTARG = anyelement,
COMMUTATOR = "==="
);
如果两个参数都是未定义的NULL值,这将失败:
test=> SELECT NULL === NULL;
ERROR: could not determine polymorphic type because input has type unknown
一种解决方法是使用重载,为您需要的每种类型定义相同的函数和运算符:
CREATE FUNCTION ndist(integer, integer) RETURNS boolean
IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
AS 'SELECT $1 IS NOT DISTINCT FROM $2';
CREATE FUNCTION ndist(text, text) RETURNS boolean
IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
AS 'SELECT $1 IS NOT DISTINCT FROM $2';
CREATE OPERATOR === (
PROCEDURE = ndist,
LEFTARG = integer,
RIGHTARG = integer,
COMMUTATOR = "==="
);
CREATE OPERATOR === (
PROCEDURE = ndist,
LEFTARG = text,
RIGHTARG = text,
COMMUTATOR = "==="
);
那么这个例子就能够运行:
test=> SELECT NULL === NULL;
?column?
t
(1 row)
这是因为类型解析规则将更喜欢在这种情况下使用
text
上的运算符。
但是,所有这些都不能让您创建排除约束,因为您的运算符没有与操作类相关联,必须确定要使用哪种索引。
您需要为每个
btree
索引方法策略 创建匹配函数,并针对这些函数定义一个
btree
的操作类。
CREATE OPERATOR
定义自己的运算符。 - clemens