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