pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: DalTech - CTE <CTE@Dal.Ca>
To: Thomas Swan <tswan@olemiss.edu>
Cc: Pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Functions with Null Arguments?
Date: Tue, 15 Aug 2000 09:12:58 -0300
Message-ID: <008601c006b2$26763400$5911be86@cte.Dal.Ca> (raw)
References: <5.0.0.11.2.20000815010809.01e52570@sunset.backbone.olemiss.edu>

I would try using COALESCE(list) which will return the first non-NULL in the list.
  ----- Original Message ----- 
  From: Thomas Swan 
  To: pgsql-sql@postgresql.org 
  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';
   
  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." 

view thread (5+ messages)  latest in thread

Message-ID: <008601c006b2$26763400$5911be86@cte.Dal.Ca>
Permalink:  ../008601c006b2$26763400$5911be86@cte.Dal.Ca/
Also on:    postgresql.org/message-id/008601c006b2$26763400$5911be86@cte.Dal.Ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: CTE@Dal.Ca, tswan@olemiss.edu
  Subject: Re: Functions with Null Arguments?
  In-Reply-To: <008601c006b2$26763400$5911be86@cte.Dal.Ca>
  References: <5.0.0.11.2.20000815010809.01e52570@sunset.backbone.olemiss.edu> <008601c006b2$26763400$5911be86@cte.Dal.Ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox