Thread

1. Bug in how nulls are handled by plpgsql?

   Jonathan Ellis <jellis@advocast.com> — 2000-11-06T17:53:12Z

   bf2=# create function foo (varchar, varchar) returns varchar as '
   begin
       return $1;
   end;
   ' language 'plpgsql';
   
   bf2'# bf2'# bf2'# bf2'# CREATE
   bf2=# bf2=# select foo('asdf', null) from dual;
    foo
   -----
   
   (1 row)
   
   Even though the function foo never references the null, apparently postgres
   thinks, "oh, a function call with a null in it.  Must be null." and doesn't
   even bother executing the function.  Bug?
   
   On another null-related note, is there any way to make
   select 'asdf' || null from dual
   
   return 'asdf' without changing it to
   select 'asdf' || coalesce(null, '') from dual
   
   ?
   
   -Jonathan
   
   
   

2. Re: Bug in how nulls are handled by plpgsql?

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-11-07T16:54:11Z

   Under 7.0.x and earlier there's only one flag to hold
   the isnull state of all parameters.  If any are null all are
   null and I think there's a related output issue.  Under current
   sources this is done the way you'd expect.
   
   As for the second question, not really that I can think of, but that
   behavior is correct per SQL. 
   
   Stephan Szabo
   sszabo@bigpanda.com
   
   On Mon, 6 Nov 2000, Jonathan Ellis wrote:
   
   > bf2=# create function foo (varchar, varchar) returns varchar as '
   > begin
   >     return $1;
   > end;
   > ' language 'plpgsql';
   > 
   > bf2'# bf2'# bf2'# bf2'# CREATE
   > bf2=# bf2=# select foo('asdf', null) from dual;
   >  foo
   > -----
   > 
   > (1 row)
   > 
   > Even though the function foo never references the null, apparently postgres
   > thinks, "oh, a function call with a null in it.  Must be null." and doesn't
   > even bother executing the function.  Bug?
   > 
   > On another null-related note, is there any way to make
   > select 'asdf' || null from dual
   > 
   > return 'asdf' without changing it to
   > select 'asdf' || coalesce(null, '') from dual
   > 
   > ?
   > 
   > -Jonathan
   >