Обсуждение: Why isn't that null

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

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

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

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