Re: comparing null value in plpgsql.

SQL uses tri-state logic. If any value in an expression is unknown, the
answer is unknown. The only time you can get a true or false result
from an expression is if all values are known. In SQL, null represents
an unknown value - it does not represent an empty value (such as a zero
length string). To catch all possible results of a SQL boolean
expression, you would need to do something like the following even
though it may look illogical:

case <expression>
when true then ...
when false then ...
else ...
end

or in PL/PGSQL:

if <expression> then
... -- true case
else
if !<expression> then
... -- false case
else
... -- unknown case
end if;
end if;

In your expression, 'if new.comp_code != old.comp_code', here are the
possibilities:

-- Both new.comp_code and old.comp_code have values. The result of the
expression is true if these values are unequal and false if they are
equal.

-- Either new.comp_code or old.comp_code or both are a null. The result
of the expression is unknown since at least one element in the
expression is unknown.

To handle situations where an element in an expression may be null, you
need to explicitly check for nulls using 'is [not] null'. You may be
able to use PostgreSQL's coalesce function to provide a default value to
use in place of a null.

If you want your code to execute when a field changes and your
definition of change includes storing a value into a field that was null
or storing a null into a field that had a value, you need to express all
of that in your condition.

if (new.comp_code != old.comp_code) or
(new.comp_code is null and old.comp_code is not null) or
(new.comp_code is not null and old.comp_code is null) then
...
end if;

This is all standard SQL.

-----Original Message-----
From: bhuvanbk(at)yahoo(dot)com [mailto:bhuvanbk(at)yahoo(dot)com]
Sent: Monday, March 11, 2002 2:03 AM
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] comparing null value in plpgsql.

On Mar 10, Stephan Szabo wrote:

> On Mon, 11 Mar 2002, Bhuvan A wrote:
>
> > here i have a problem in comparing null values in plpgsql. this
exist
> > in 7.1.x and 7.2 as well.
> >
> > the condition <null value> != <valid value> fails in plpgsql.
> > consider this function is triggered on every updation on a table.
> >
> > create function ftest()
> > returns opaque as 'declare
> >
> > begin
> >
> > if new.comp_code != old.comp_code then
> > ...
> > end if;
> > return new;
> > end;'
> > language 'plpgsql';
> >
> > this condition fails if old.comp_code is null and new.comp_code has
> > some value.
>
> <nullvalue> != <anything> is not true, it's unknown, so the if
shouldn't

what do you mean by <nullvalue> != <anything>?

> fire. It's a side effect of how the spec defines operations on nulls.
>

here i mean..

if <nullvalue> != <not null value>, the loop is not fired. why?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)