On Nov 25, 2005, at 11:17 , Christopher Kings-Lynne wrote:
> Hi guys,
>
> Does anyone know how I'd go about implementing the following MySQL
> operator in PostgreSQL?
I'm sure you know how to implement this with a stored procedure.
AFAICT, if you wanted to actually implement this as an operator,
you'd need to write C procedures for each datatype to make it an
operator. Is that something you're looking at doing?
Michael Glaesemann
grzm myrealbox com
create or replace function null_safe_cmp (integer, integer) returns int
immutable language sql as $$
select case when $1 is null and $2 is null then 1 when ($1 is null and $2 is not null) or ($1 is not null
and$2 is null) then 0 else case when $1 = $2 then 1 else 0 end
end;
$$;
test=# select null_safe_cmp(1,1);
null_safe_cmp
--------------- 1
(1 row)
test=# select null_safe_cmp(0,1);
null_safe_cmp
--------------- 0
(1 row)
test=# select null_safe_cmp(1,0);
null_safe_cmp
--------------- 0
(1 row)
test=# select null_safe_cmp(NULL,1);
null_safe_cmp
--------------- 0
(1 row)
test=# select null_safe_cmp(1,NULL);
null_safe_cmp
--------------- 0
(1 row)
test=# select null_safe_cmp(NULL,NULL);
null_safe_cmp
--------------- 1
(1 row)
