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