pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql help - nested loops
3+ messages / 2 participants
[nested] [flat]
* plpgsql help - nested loops
@ 2001-01-02 23:20 Cedar Cox <cedarc@visionforisrael.com>
2001-01-03 14:16 ` Re: plpgsql help - nested loops Robert B. Easter <reaster@comptechnews.com>
0 siblings, 1 reply; 3+ messages in thread
From: Cedar Cox @ 2001-01-02 23:20 UTC (permalink / raw)
To: pgsql-interfaces
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
Attachments:
matchitems (text/plain, 3.9K) download
CREATE FUNCTION match_items(int4) RETURNS bool AS '
declare
varmatchid alias for $1;
varqty tblListInventoryItems_cache.Each%TYPE;
-- request and shipment record loop vars
rloop record;
sloop record;
begin
-- find (each itemuniqueid or varmatchid) with inventorylevelid=3, ie. does not have a location, locationid=0
for rloop in select cache.*, r.requestdate
from tbllistinventoryitems_cache as cache, tbllistrequestsentitems as rsi, tbllistrequestitems as ri, tbllistrequests as r
where cache.itemuniqueid=rsi.itemuniqueid and rsi.requestitemid=ri.itemuniqueid and ri.requestid=r.requestid
and cache.inventorylevelid=3
and (varmatchid=null or varmatchid=cache.itemuniqueid)
order by cache.urgencyid, r.requestdate
loop
varqty := 0 - rloop.each;
raise notice ''looking for match for requestitem % qty %'',rloop.itemuniqueid,varqty;
-- for each matching inventory item that is in the warehouse, ie. inventorylevelid=1
--XXX serial number
for sloop in select sum(each), serialnumber, bestbefore, locationid
from tbllistinventoryitems_cache
where treeid=rloop.treeid and sc1optionid=rloop.sc1optionid and sc2optionid=rloop.sc2optionid
and sc3optionid=rloop.sc3optionid
-- and serialnumber=rloop.serialnumber
and inventorylevelid<=2
group by treeid, sc1optionid, sc2optionid, sc3optionid, serialnumber, bestbefore, locationid
having sum(each)>0
order by bestbefore, sum
loop
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 above sql found at least 1 match
-- raise notice '' available qty (sum(each)) = %, requestqty (varqty) = %'',sloop.sum,varqty;
-- if qty available is less than requested qty, insert request for qty available at that location
if sloop.sum < varqty then
raise notice ''** insert identical where itemuniqueid=%, qty=%, locationid=%'', rloop.itemuniqueid, sloop.sum, sloop.locationid;
insert into tbllistrequestsentitems select itemuniqueid, requestitemid, treeid,
sc1optionid, sc2optionid, sc3optionid, serialnumber, bestbefore,
sloop.sum, 4, sloop.locationid
from tbllistrequestsentitems where itemuniqueid=rloop.itemuniqueid;
else -- qty available at this location equal to or greater then requested qty
-- we can match everything
raise notice ''** update where itemuniqueid=%, qty=%, unitid=4, locationid=%'',rloop.itemuniqueid,varqty,sloop.locationid;
update tbllistrequestsentitems set qty=varqty, unitid=4, locationid=sloop.locationid where itemuniqueid=rloop.itemuniqueid;
end if;
-- decrement requested qty by available, may leave requested qty negative
varqty := varqty - sloop.sum;
-- stop searching shipment items (sloop), we are done with this request item, go to the next request (rloop)
exit when varqty<=0;
end loop;
if FOUND then
raise notice ''FOUND'';
else
raise notice ''not FOUND'';
end if;
if FOUND and varqty>0 then
-- if the shipment loop (sloop) found anything we must have modified something so
-- we need to check qty of remaining unmatched items
-- if whats remaining>0 then we did not match everything, update whats left with no location
-- note: this will trigger another call to match_items(),
-- this is why we need to check if the shipment loop modified anything
raise notice ''** update where itemuniqueid=%, qty=%, unitid=4 (locationid implicit 0)'',rloop.itemuniqueid,varqty;
update tbllistrequestsentitems set qty=varqty, unitid=4 where itemuniqueid=rloop.itemuniqueid;
end if;
end loop;
return true;
end;
' LANGUAGE 'plpgsql';
* Re: plpgsql help - nested loops
2001-01-02 23:20 plpgsql help - nested loops Cedar Cox <cedarc@visionforisrael.com>
@ 2001-01-03 14:16 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-03 20:13 ` Re: plpgsql help - nested loops Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert B. Easter @ 2001-01-03 14:16 UTC (permalink / raw)
To: Cedar Cox <cedarc@visionforisrael.com>; pgsql-interfaces
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@comptechnews.com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql help - nested loops
2001-01-02 23:20 plpgsql help - nested loops Cedar Cox <cedarc@visionforisrael.com>
2001-01-03 14:16 ` Re: plpgsql help - nested loops Robert B. Easter <reaster@comptechnews.com>
@ 2001-01-03 20:13 ` Cedar Cox <cedarc@visionforisrael.com>
0 siblings, 0 replies; 3+ messages in thread
From: Cedar Cox @ 2001-01-03 20:13 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: pgsql-interfaces
On Wed, 3 Jan 2001, Robert B. Easter wrote:
> 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.
Ok, makes sense.. This should be, if there isn't, documented somewhere
about the rewriting. So I guess the proper way would be more like the
second example except using 'is':
if sloop.serialnumber=rloop.serialnumber or
(sloop.serialnumber is null and rloop.serialnumber is null) then
raise notice '' SN match'';
else
raise notice '' SN mis-match'';
end if;
Of course, I'd use isnull instead. I wish I didn't have to type that
little bit more just to compare two variables.. ;)
Thanks,
-Cedar
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-01-03 20:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-02 23:20 plpgsql help - nested loops Cedar Cox <cedarc@visionforisrael.com>
2001-01-03 14:16 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-03 20:13 ` Cedar Cox <cedarc@visionforisrael.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox