Re: NULL safe equality operator

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)