Re: NULL safe equality operator

[Michael Glaesemann <grzm(at)myrealbox(dot)com>]

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