>>> "Ian Sillitoe" <ian.sillitoe@googlemail.com> 03/04/08 5:49 AM >>>
I'm trying to JOIN two tables (well a table and a resultset from a PL/pgsql
function) where a joining column can be NULL
In a join, no value can be ascribed to a null field, so the equivalence fails. You can do tests like IS NULL, which
strictlyspeaking is test for meeting a condition (that of not having any value), not a test for equivalence. As
(simplistically)the condition NULL does equal the condition NULL, (NULL = NULL) is true.
The simplest approach is perhaps to have a value which does not occur naturally (like -1), as a substitute for nulls in
therelevant columns. I believe this can be achieved via a view in your case, (pun intended :-), but which may be less
efficientif performance is an issue:
create view depth_v as
select d.id,
d.name,
case when (d.depth1 is null) then -1 else d.depth1 end as depth1,
case when (d.depth2 is null) then -1 else d.depth2 end as depth2,
case when (d.depth3 is null) then -1 else d.depth3 end as depth3,
case when (d.depth4 is null) then -1 else d.depth4 end as depth4,
case when (d.depth5 is null) then -1 else d.depth5 end as depth5
from depth_table d;
You could then join against this view instead of your underlying table, eg:
select c.* from get_cathcode('1.10.8') c JOIN depth_v t USING(depth1, depth2, depth3, depth4);
The view will not have any NULL values in the depth fields, so the join should work.
see: http://www.postgresql.org/docs/8.2/static/functions-conditional.html
(Incidentally, if you are storing bathymetry or CTD data, I'd be interested in seeing your db structures, as I may be
doingsome work in that area soon :-)
HTH,
Brent Wood
