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