Thread: Functions with Null Arguments?
<br /> Is there anyway way to get the following to work?<br /><br /><font face="Courier, Courier">table foo<br /> id | name1 | name2<br /> ---+---------+------<br /> 0 | Abe | NULL<br /> 1 | William | Bill<br /><br /><br /></font>createfunction prefname(text, text)<br /> returns text as'<br /> declare<br /> name1 alias for $1;<br /> name2 alias for $2;<br /> begin<br /> if name2 isnull<br /> then<br /> return name1;<br/> else<br /> return name2;<br /> end if;<br /> end;'<br /> language 'plpgsql';<br/> <br /> if I do<br /> select id, name1, name2, prefname(name1, name2) as pref from foo;<br /><br /> I windup with <br /><br /><font face="Courier, Courier">id | name1 | name2 | prefname<br /> ---+---------+-------+-----------<br/> 0 | Abe | NULL | NULL<br /> 1 | William | Bill | Bill<br /><br /></font>insteadof <br /><br /><font face="Courier, Courier">id | name1 | name2 | prefname<br /> ---+---------+-------+-----------<br/> 0 | Abe | NULL | Abe<br /> 1 | William | Bill | Bill<br /><br /></font> Iassume this has to do with Postgres not executing the function is one of the arguments is missing...<br /><br /><br /> -<br /> - <b><u>Thomas Swan</u></b> <br /> - Graduate Student - Computer Science<br />- The University of Mississippi<br /> - <br /> - "People can be categorized into two fundamental <br /> - groups, thosethat divide people into two groups <br /> - and those that don't."
I would try using COALESCE(list) which will return the first non-NULL in the list.
----- Original Message -----From: Thomas SwanSent: Tuesday, August 15, 2000 3:18 AMSubject: [SQL] Functions with Null Arguments?
Is there anyway way to get the following to work?
table foo
id | name1 | name2
---+---------+------
0 | Abe | NULL
1 | William | Bill
create function prefname(text, text)
returns text as'
declare
name1 alias for $1;
name2 alias for $2;
begin
if name2 isnull
then
return name1;
else
return name2;
end if;
end;'
language 'plpgsql';
if I do
select id, name1, name2, prefname(name1, name2) as pref from foo;
I wind up with
id | name1 | name2 | prefname
---+---------+-------+-----------
0 | Abe | NULL | NULL
1 | William | Bill | Bill
instead of
id | name1 | name2 | prefname
---+---------+-------+-----------
0 | Abe | NULL | Abe
1 | William | Bill | Bill
I assume this has to do with Postgres not executing the function is one of the arguments is missing...
-
- 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."
I think you could use a function like the one below and use the following
select:
select id, name1, name2, prefname(foo) as pref from foo;
create function prefname(foo)
returns text as'
declare foo alias for $1;
begin if foo.name2 is null then return foo.name1; else return foo.name2;
end if;
end;'
language 'plpgsql';
--
Jesus Aneiros Sosa
mailto:aneiros@jagua.cfg.sld.cu
http://jagua.cfg.sld.cu/~aneiros
On Tue, 15 Aug 2000, Thomas Swan wrote:
>
> Is there anyway way to get the following to work?
>
> table foo
> id | name1 | name2
> ---+---------+------
> 0 | Abe | NULL
> 1 | William | Bill
>
>
> create function prefname(text, text)
> returns text as'
> declare
> name1 alias for $1;
> name2 alias for $2;
> begin
> if name2 isnull
> then
> return name1;
> else
> return name2;
> end if;
> end;'
> language 'plpgsql';
>
> if I do
>
> I wind up with
>
> id | name1 | name2 | prefname
> ---+---------+-------+-----------
> 0 | Abe | NULL | NULL
> 1 | William | Bill | Bill
>
> instead of
>
> id | name1 | name2 | prefname
> ---+---------+-------+-----------
> 0 | Abe | NULL | Abe
> 1 | William | Bill | Bill
>
> I assume this has to do with Postgres not executing the function is one of
> the arguments is missing...
>
>
> -
> - 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."
At 10:12 AM 8/15/2000, DalTech - CTE wrote:<br /><blockquote cite="cite" type="cite"><font size="2">I would try using </font>COALESCE(<tt>list</tt>)<fontsize="2"> which will return the first non-NULL in the list.</font></blockquote><br />That worked! Thanks!<br /><br /> The second part is why does postgresql not evaluate the function if one of the argumentsis null?<br />
At 01:24 PM 8/15/2000, DalTech - CTE wrote:<br /><blockquote cite="cite" type="cite"><font size="2">Known bug in some versions. I believe it was fixed in v7.x, though.</font><br /> <br /><font size="2">Cheers....</font><dl><dd>----- OriginalMessage ----- <dd>From: <a href="mailto:tswan-lst@tangent.ics.olemiss.edu">Thomas Swan</a><dd>To: <a href="mailto:CTE@Dal.Ca">DalTech- CTE</a> ; <a href="mailto:tswan@olemiss.edu">Thomas Swan</a><dd>Cc: <a href="mailto:pgsql-sql@postgresql.org">Pgsql-sql</a><dd>Sent:Tuesday, August 15, 2000 1:43 PM <dd>Subject: Re: [SQL] Functionswith Null Arguments?<br /><br /><dd>At 10:12 AM 8/15/2000, DalTech - CTE wrote:<blockquote cite="cite" type="cite"><fontsize="2"> </font></blockquote><dd>I would try using COALESCE(<tt>list</tt>)<font size="2"> which will returnthe first non-NULL in the list.</font></dl></blockquote><dl><dd>That worked! Thanks!<br /><br /><dd>The second partis why does postgresql not evaluate the function if one of the arguments is null? </dl><br /> I'm using version 7.0.2(x86 Linux)