Re: NULL safe equality operator

On Nov 25, 2005, at 16:43 , Christopher Kings-Lynne wrote:

>> Yeah, I saw your commit. Nice shortcut. Also didn't know you
>> could define operators using SQL functions. Tom's suggestion of
>> NOT (a DISTINCT FROM b) is really cool. Much cleaner in my
>> opinion. I learn a lot from these lists :)
>
> Needs to return 0 or 1 though.

CREATE OR REPLACE FUNCTION null_safe_cmp (ANYELEMENT, ANYELEMENT)
RETURNS INTEGER IMMUTABLE
LANGUAGE SQL AS $$
SELECT CASE
WHEN NOT ($1 IS DISTINCT FROM $2) THEN 1
ELSE 0
END;
$$;

select null_safe_cmp (1,1) as "(1,1)"
, null_safe_cmp (1,0) as "(1,0)"
, null_safe_cmp (1,NULL) as "(1,NULL)"
, null_safe_cmp (NULL,1) as "(NULL,1)"
, null_safe_cmp (NULL::integer,NULL::integer) as "(NULL,NULL)";
(1,1) | (1,0) | (1,NULL) | (NULL,1) | (NULL,NULL)
-------+-------+----------+----------+-------------
1 | 0 | 0 | 0 | 1
(1 row)

test=# select null_safe_cmp (NULL,NULL);
ERROR: could not determine anyarray/anyelement type because input
has type "unknown"
test=# select null_safe_cmp (NULL::integer,NULL::integer);
null_safe_cmp
---------------
1
(1 row)

Same casting problem due to anyelement, of course.

Michael Glaesemann
grzm myrealbox com