Received: from tangent.ics.olemiss.edu (IDENT:root@tangent.ics.olemiss.edu [130.74.31.3]) by hub.org (8.10.1/8.10.1) with ESMTP id e7F6NNn90950 for ; Tue, 15 Aug 2000 02:23:23 -0400 (EDT) Received: from Betelgeuse.olemiss.edu (wsp000771wss.ics.olemiss.edu [130.74.31.251]) by tangent.ics.olemiss.edu (8.9.3/8.8.7) with ESMTP id BAA08741 for ; Tue, 15 Aug 2000 01:23:22 -0500 Message-Id: <5.0.0.11.2.20000815010809.01e52570@sunset.backbone.olemiss.edu> X-Sender: tswan@sunset.backbone.olemiss.edu X-Mailer: QUALCOMM Windows Eudora Version 5.0.0.11 (Beta) Date: Tue, 15 Aug 2000 01:18:14 -0500 To: pgsql-sql@postgresql.org From: Thomas Swan Subject: Functions with Null Arguments? Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_9517218==_.ALT" X-Archive-Number: 200008/99 --=====================_9517218==_.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed 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." --=====================_9517218==_.ALT Content-Type: text/html; charset="us-ascii"
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." --=====================_9517218==_.ALT--