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