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)