Re: Passing a null value in pl/pgsql

Mike Finn <mike(dot)finn(at)tacticalExecutive(dot)com> writes:
> If I temporarily (for testing only) change the asterisked line to read
> and seqSalesOrderLine = null
> the query works as I would expect.

Unfortunately "NULL = NULL" does not return TRUE in SQL, it returns
NULL, which is treated as FALSE in the context of a WHERE clause.

The reason the hacked query appears to work is that "foo = NULL" (where
the word NULL appears explicitly) is presently converted to "foo IS NULL"
(the behavior you actually want) by the Postgres parser. This behavior
is not valid per standard and has confused a lot of people besides you,
so it's going away in the next release.

If you want to have NULL in your parameter match up with NULL in your
database then you'll need to write something like

(seqSalesOrderLine = _seqSalesOrderLine OR
(seqSalesOrderLine IS NULL AND _seqSalesOrderLine IS NULL))

which is not only ugly as sin but probably won't run quickly, since it
won't be indexable.

I counsel rethinking how you are using NULLs ... if you are trying to
search for them then you probably aren't using them the way SQL expects,
and you are going to find yourself constantly fighting the language
rather than working comfortably with it.

regards, tom lane