Thread

1. Why isn't that null

   Matthias Teege <matthias@mteege.de> — 2000-06-27T06:52:37Z

   Moin,
   
   I have al small problem with ISNULL in a trigger
   function. My definition looks like: 
   
   IF NEW.vkp ISNULL THEN
      select t1.las into i_kd 
      from auftrag t1, aufpos t2 where 
      t2.auftrag = t1.id;
      select get_vkp(i_kd, NEW.artikel) into f_vkp;
      NEW.vkp:=f_vkp;
   END IF;
   
   The following SQL query didnt cause this if statement: 
   
   insert into aufpos(auftrag, artikel, best_menge, vkp,
   lieferwoche, cuser, ctime, uuser, utime) values
   ('175','8501900','12','','3500','matthias',
   now (), 'matthias',now ())
   
   If I change the function statement as follows: 
   
   IF NEW.vkp = 0 THEN
      ... 
   END IF;
   
   it works but '0' is a leagal value but not '' (empty). The
   function should only be called if NEW.vkp ist empty.
   
   Many thanks
   Matthias
   
   
   
   

2. Re: Why isn't that null

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-27T08:47:42Z

   Matthias Teege <matthias@mteege.de> writes:
   > I have al small problem with ISNULL in a trigger
   > function. My definition looks like: 
   
   > IF NEW.vkp ISNULL THEN
   >    ...
   
   > The following SQL query didnt cause this if statement: 
   
   > insert into aufpos(auftrag, artikel, best_menge, vkp,
   > lieferwoche, cuser, ctime, uuser, utime) values
   > ('175','8501900','12','','3500','matthias',
   > now (), 'matthias',now ())
   
   This seems correct to me --- an empty string '' is certainly not
   the same thing as NULL.
   
   > If I change the function statement as follows: 
   
   > IF NEW.vkp = 0 THEN
   >    ... 
   > END IF;
   
   > it works
   
   Shouldn't that give a type error of some sort?  You didn't say
   what type vkp is, but if it's a string type then comparing it
   against a numeric shouldn't work IMHO.
   
   			regards, tom lane
   
   

3. Re: Why isn't that null

   NRonayette <r97m10@socotec.fr> — 2000-06-27T08:55:46Z

   Hi,
   
   May be this insert will work with your test in your function
   
   insert into aufpos(auftrag, artikel, best_menge, vkp,
    lieferwoche, cuser, ctime, uuser, utime) values
   ('175','8501900','12',NULL,'3500','matthias',
    now (), 'matthias',now ())
   
   Nicolas
   
   Matthias Teege a écrit :
   > 
   > Moin,
   > 
   > I have al small problem with ISNULL in a trigger
   > function. My definition looks like:
   > 
   > IF NEW.vkp ISNULL THEN
   >    select t1.las into i_kd
   >    from auftrag t1, aufpos t2 where
   >    t2.auftrag = t1.id;
   >    select get_vkp(i_kd, NEW.artikel) into f_vkp;
   >    NEW.vkp:=f_vkp;
   > END IF;
   > 
   > The following SQL query didnt cause this if statement:
   > 
   > insert into aufpos(auftrag, artikel, best_menge, vkp,
   > lieferwoche, cuser, ctime, uuser, utime) values
   > ('175','8501900','12','','3500','matthias',
   > now (), 'matthias',now ())
   > 
   > If I change the function statement as follows:
   > 
   > IF NEW.vkp = 0 THEN
   >    ...
   > END IF;
   > 
   > it works but '0' is a leagal value but not '' (empty). The
   > function should only be called if NEW.vkp ist empty.
   > 
   > Many thanks
   > Matthias
   
   

4. Re: Why isn't that null

   Matthias Teege <matthias@mteege.de> — 2000-06-27T13:55:33Z

   Tom Lane <tgl@sss.pgh.pa.us> writes:
   
   [...]
   
   > > If I change the function statement as follows: 
   > 
   > > IF NEW.vkp = 0 THEN
   > >    ... 
   > > END IF;
   > 
   > > it works
   > 
   > Shouldn't that give a type error of some sort?  You didn't say
   > what type vkp is, but if it's a string type then comparing it
   > against a numeric shouldn't work IMHO.
   
   no it isn't. It is a float8 type. I changed my program in
   that way that the query includes NULL instead of ''.
   
   Bis dann
   Matthias
   
   
   -- 
   Matthias Teege -- matthias@mteege.de -- http://emugs.de
   make world not war
   PGP-Key auf Anfrage