Re: plpgsql help - nested loops

On Wed, 3 Jan 2001, Robert B. Easter wrote:

> See:
> http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic
> 
> I think it might clarify the situation.  If you find any errors on the page, 
> please let me know.
> 
> Ordinary equality comparions (=,>=,<=) between a NULL and anything else 
> always results in NULL, normally.  NULL in boolean comparisons, IS, IS NOT, 
> AND, and OR can give different results.  Your equals comparison was always 
> giving NULL when comparing the two fields together directly.  You were 
> getting a confusing result when comparing equality of one field directly with 
> NULL, which normally would be NULL too except that PostgreSQL is doing some 
> rewriting of the expressing behind your back, changing anything it sees with 
> an equality operator and a literal NULL into a boolean comparison "field IS 
> NULL" (true if field is NULL) instead of "field = NULL" (normally always NULL 
> in the absence of a write you aren't seeing within the database).
> 
> I think that is what the deal is.  The proper way to check for null, if it is 
> a possibility (no NOT NULL constraint), is to use boolean operators (IS, IS 
> NOT) explicitly to check.

Ok, makes sense.. This should be, if there isn't, documented somewhere
about the rewriting.  So I guess the proper way would be more like the
second example except using 'is':
 if sloop.serialnumber=rloop.serialnumber or   (sloop.serialnumber is null and rloop.serialnumber is null) then   raise
notice'' SN match''; else   raise notice '' SN mis-match''; end if;
 

Of course, I'd use isnull instead.  I wish I didn't have to type that
little bit more just to compare two variables.. ;)

Thanks,
-Cedar