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
