Passing a null value in pl/pgsql

From: Mike Finn <mike(dot)finn(at)tacticalExecutive(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Passing a null value in pl/pgsql
Date: 2002-01-14 20:00:04
Message-ID: a1vdac$6dj$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)finn(at)tacticalExecutive(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message wsheldah 2002-01-14 20:06:45 Re: Integrating ADSM
Previous Message Chris Albertson 2002-01-14 19:39:57 Re: Very large database