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

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."
Tue, 15 Aug 2000 14:04:39 -0500
Thomas Swan <tswan@olemiss.edu>
Functions and Null Values
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

Fri, 18 Aug 2000 23:20:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Functions and Null Values 