Re: plpgsql help - nested loops

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.

On Tuesday 02 January 2001 18:20, Cedar Cox wrote:

> > I'm trying to compare the fields of two loop record variables. The loops
> are nested, the compare is done inside the inner one. If given this:
>
> raise notice ''sloop.serialnumber=%, rloop.serialnumber=%'',
> sloop.serialnumber, rloop.serialnumber;
>
> if sloop.serialnumber=rloop.serialnumber then
> raise notice '' SN match'';
> else
> raise notice '' SN mis-match'';
> end if;
>
> the output is
>
> NOTICE: sloop.serialnumber=<NULL>, rloop.serialnumber=<NULL>
> NOTICE: SN mis-match
>
> However, if you change the comparison to
>
> if sloop.serialnumber=rloop.serialnumber or
> (sloop.serialnumber=null and rloop.serialnumber=null) then
> raise notice '' SN match'';
> else
> raise notice '' SN mis-match'';
> end if;
>
> everything is fine.
>
> Question: does null=null evaluate to true, false, or null? If in psql you
> do SELECT null=null; it returns true. Am I missing something? Attached
> is the full trigger code..
>
> Thanks,
> -Cedar

----------------------------------------
Content-Type: TEXT/PLAIN; name="matchitems"
Content-Transfer-Encoding: BASE64
Content-Description:
----------------------------------------

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------