Received: from Alex2.cte.Dal.Ca (Alex2.cte.Dal.Ca [134.190.17.62]) by hub.org (8.10.1/8.10.1) with ESMTP id e7FCA1n12553 for ; Tue, 15 Aug 2000 08:10:01 -0400 (EDT) Received: from ghost (Ghost.cte.Dal.Ca [134.190.17.89]) by Alex2.cte.Dal.Ca (8.9.3/8.9.3) with SMTP id JAA21012; Tue, 15 Aug 2000 09:24:09 -0300 Message-ID: <008601c006b2$26763400$5911be86@cte.Dal.Ca> From: "DalTech - CTE" To: "Thomas Swan" Cc: "Pgsql-sql" References: <5.0.0.11.2.20000815010809.01e52570@sunset.backbone.olemiss.edu> Subject: Re: Functions with Null Arguments? Date: Tue, 15 Aug 2000 09:12:58 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0083_01C00699.00FFC920" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200008/104 This is a multi-part message in MIME format. ------=_NextPart_000_0083_01C00699.00FFC920 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I would try using COALESCE(list) which will return the first non-NULL in th= e list. ----- Original Message -----=20 From: Thomas Swan=20 To: pgsql-sql@postgresql.org=20 Sent: Tuesday, August 15, 2000 3:18 AM Subject: [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'; =20=20=20 if I do select id, name1, name2, prefname(name1, name2) as pref from foo; I wind up with=20 id | name1 | name2 | prefname ---+---------+-------+----------- 0 | Abe | NULL | NULL 1 | William | Bill | Bill instead of=20 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 o= f the arguments is missing... -=20 - Thomas Swan=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 - Graduate Student - Computer Science - The University of Mississippi -=20 - "People can be categorized into two fundamental=20 - groups, those that divide people into two groups=20 - and those that don't."=20 ------=_NextPart_000_0083_01C00699.00FFC920 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I would try using COALESCE(list) which will retu= rn the=20 first non-NULL in the list.
----- Original Message -----
Fro= m:=20 Thomas Swa= n=20
To: pgsql-sql@postgresql.org
Sent: Tuesday, August 15, 2000 3:1= 8=20 AM
Subject: [SQL] Functions with Null= =20 Arguments?


Is there anyway way to get the following to=20 work?

table foo
id | name1 = ; =20 | name2
---+---------+------
 0 | Abe     = |=20 NULL
 1 | William | Bill


create function=20 prefname(text, text)
returns text=20 as'
declare
        = name1=20 alias for=20 $1;
        nam= e2=20 alias for=20 $2;
begin
        if=20 name2=20 isnull
        = then
                
return=20 name1;
        = else
                return=20 name2;
        = end=20 if;
end;'
language 'plpgsql';
 
if I=20 do
select id, name1, name2, prefname(name1, name2) as pref from=20 foo;

I wind up with

id |= =20 name1   | name2 |=20 prefname
---+---------+-------+-----------
 0 |=20 Abe     | NULL  | NULL
 1 | William |=20 Bill  | Bill

instead of

id | name1   | name2 |=20 prefname
---+---------+-------+-----------
 0 |=20 Abe     | NULL  | Abe
 1 | William |=20 Bill  | Bill

I assume this has to do with= =20 Postgres not executing the function is one of the arguments is=20 missing...


-
- Thomas Swan=20         &nb= sp;                 =         
-=20 Graduate Student  - Computer Science
- The University of=20 Mississippi
-
- "People can be categorized into two fundamental -=20 groups, those that divide people into two groups
- and those that don= 't."=20 ------=_NextPart_000_0083_01C00699.00FFC920--