Re: When is a record NULL?

On Jul 24, 2009, at 9:40 AM, Jeff Davis wrote:

> There's no single test, but you can see if it's an empty row with:
>  x IS NULL AND x IS DISTINCT FROM NULL
> And you can see if it's a "real" NULL by:
>  x IS NULL AND x IS NOT DISTINCT FROM NULL
>
> It's funny until you try it -- then it's just scary.

Amen to that.

So here's what I'm doing, essentially (some error handling removed for  
clarity):
    FETCH have INTO rec_have;    FETCH want INTO rec_want;    WHILE NOT rec_have IS NULL OR NOT rec_want IS NULL LOOP
    IF rec_have IS DISTINCT FROM rec_want THEN            RETURN false;        END IF;        rownum = rownum + 1;
 FETCH have INTO rec_have;        FETCH want INTO rec_want;    END LOOP;    RETURN true;
 

So far this seems to work for the tests I've thrown at it, telling me  
when two cursors return results that are row-by-row equivalent,  
including when columns have the NULLs, though I've not yet tried rows  
that are nothing but nulls. That's probably not very useful, but it is  
possible.

Does this look like it's a reasonable implementation for what I'm  
testing? Have I missed anything in the swirl of the bizarre that this  
thread has triggered?

Thanks,

David