Thread: Passing a null value in pl/pgsql

I have two stored procedures and one calls the other.  If
the caller passes a null for one of the parameters (the fourth) a delete
query in the called function will not work.

If I pass a null as the 4th parameter (_seqSalesOrderLine) the delete
query below will not match any records, and my business rule fails.  If I
temporarily (for testing only) change the asterisked line to read
   and seqSalesOrderLine = null
the query works as I would expect.

Am I missing something (please tell me) or is this a bug when passing in a
null value?

Thanks for any help,
Mike.


Bits of called function (asterisks mark important lines)
===========================================================================
    create function salesOrderTax_apply(int8,int8,
                                        text,int8,float8,
                                        int8,text) returns integer as '
        declare
            ...
*           _seqSalesOrderLine  alias for $4;
            ...
        begin
            ...
            delete from salesOrderTax
            where seqSalesOrder = soRec.seqSalesOrder
            and codeSalesOrderTax = _codeSalesOrderTax
*           and seqSalesOrderLine = _seqSalesOrderLine
            ;
            ...
            return null; --bogus return value
        end;
    ' language 'plpgsql';


Bits of calling function (asterisks mark important lines)
===========================================================================
    create function salesOrderControl_freightTax(int8, int8,text)
                                                  returns integer as '
        declare
            ...
            _seqTaxSchedule    int8;
            ...
        begin
            ...
            -- get the appropriate tax schedule
            select seqTaxSchedule
            into _seqTaxSchedule
            from carrier
            where name = soRec.carrier_name
            ;
            ...
            perform
               salesOrderTax_apply(soRec.seqSalesOrder, _seqTaxSchedule,
*                                  ''Freight'', null, socRec.totalFreight,
                                   _txSeq, _txUser
                                  );

            return null; --bogus return value
        end;
    ' language 'plpgsql';


=========================================
Mike Finn
mike.finn@tacticalExecutive.com

On Mon, 14 Jan 2002, Mike Finn wrote:

> I have two stored procedures and one calls the other.  If
> the caller passes a null for one of the parameters (the fourth) a delete
> query in the called function will not work.
>
> If I pass a null as the 4th parameter (_seqSalesOrderLine) the delete
> query below will not match any records, and my business rule fails.  If I
> temporarily (for testing only) change the asterisked line to read
>    and seqSalesOrderLine = null
> the query works as I would expect.

The only reason the above works is probably due to the hack that treats
the explicit = NULL token sequence as IS NULL which is the correct way to
compare for nulls.  SQL basically wants = between a NULL value and
anything to be unknown.

Mike Finn <mike.finn@tacticalExecutive.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