Functions and Null Values

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."