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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
