Thread

1. Functions and Null Values

   Thomas Swan <tswan@olemiss.edu> — 2000-08-15T19:04:39Z

   On v7.0.2:
   
   I have a function preferred(text, text).   It returns the second argument 
   if the second is not null or the first if the second is null.
   I understand I can use coalesce, but this is a simple case and not 
   practical but illustrates the point.
   
   If I do select col1, col2, preferred(col1, col2) as col3 col3 only contains 
   values where col2 had a non-null value.
   
   create function preferred(text, text)
   returns text
   as '
   declare
            first alias for $1;
            second alias for $2;
   begin
            if      second isnull
            then
                    return first;
            else
                    return second;
            end if;
   end;'
   language 'plpgsql';
   
   e.g.
   
   col1|col2
   ----+----
     Am | y
     Ba |NULL
     Ca | t
   
   I expect
   
   col1|col2|col3
   ----+----+-----
     Am | y  | Amy
     Ba |NULL| Ba
     Ca | t  | Cat
   
   I get
   
   col1|col2|col3
   ----+----+-----
     Am | y  | Amy
     Ba |NULL|NULL
     Ca | t  | Cat
   
   My major question is how to pass NULL values or values that could be 
   potentially NULL into the function and get a reliable result.
   
    From what I can gather the function only gets called when both values are 
   present and not when any of them are NULL.   Is it because there isn't a 
   match for preferred(text, NULL) or is it something else?
   
   
   -
   - Thomas Swan
   - Graduate Student  - Computer Science
   - The University of Mississippi
   -
   - "People can be categorized into two fundamental
   - groups, those that divide people into two groups
   - and those that don't."

2. Re: Functions and Null Values

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-19T03:20:52Z

   Thomas Swan <tswan@olemiss.edu> writes:
   > From what I can gather the function only gets called when both values are 
   > present and not when any of them are NULL.
   
   It's sillier than that: the function does actually get called, and then
   the return value is thrown away and replaced with a NULL.  This is an
   inherent limitation of the old function-call interface.  It is fixed for
   7.1 but I don't know of any good workaround for 7.0.* or before.
   
   			regards, tom lane