pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Functions with Null Arguments?
5+ messages / 4 participants
[nested] [flat]

* Functions with Null Arguments?
@ 2000-08-15 06:18 Thomas Swan <tswan@olemiss.edu>
  2000-08-15 12:12 ` Re: Functions with Null Arguments? DalTech - CTE <CTE@Dal.Ca>
  2000-08-15 16:43 ` Re: Functions with Null Arguments? Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
  2000-08-15 19:24 ` Re: Functions with Null Arguments? Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
  0 siblings, 3 replies; 5+ messages in thread

From: Thomas Swan @ 2000-08-15 06:18 UTC (permalink / raw)
  To: pgsql-sql


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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Functions with Null Arguments?
  2000-08-15 06:18 Functions with Null Arguments? Thomas Swan <tswan@olemiss.edu>
@ 2000-08-15 12:12 ` DalTech - CTE <CTE@Dal.Ca>
  2 siblings, 0 replies; 5+ messages in thread

From: DalTech - CTE @ 2000-08-15 12:12 UTC (permalink / raw)
  To: Thomas Swan <tswan@olemiss.edu>; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Functions with Null Arguments?
  2000-08-15 06:18 Functions with Null Arguments? Thomas Swan <tswan@olemiss.edu>
@ 2000-08-15 16:43 ` Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
  2000-08-15 18:28   ` Re: Functions with Null Arguments? Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
  2 siblings, 1 reply; 5+ messages in thread

From: Thomas Swan @ 2000-08-15 16:43 UTC (permalink / raw)
  To: DalTech - CTE <CTE@Dal.Ca>; Thomas Swan <tswan@olemiss.edu>; +Cc: pgsql-sql

At 10:12 AM 8/15/2000, DalTech - CTE wrote:
>I would try using COALESCE(list) which will return the first non-NULL in 
>the list.

That worked! Thanks!

The second part is why does postgresql not evaluate the function if one of 
the arguments is null?

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Functions with Null Arguments?
  2000-08-15 06:18 Functions with Null Arguments? Thomas Swan <tswan@olemiss.edu>
  2000-08-15 16:43 ` Re: Functions with Null Arguments? Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
@ 2000-08-15 18:28   ` Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
  0 siblings, 0 replies; 5+ messages in thread

From: Thomas Swan @ 2000-08-15 18:28 UTC (permalink / raw)
  To: DalTech - CTE <CTE@Dal.Ca>; +Cc: pgsql-sql

At 01:24 PM 8/15/2000, DalTech - CTE wrote:
>Known bug in some versions.  I believe it was fixed in v7.x, though.
>
>Cheers....
>----- Original Message -----
>From: <mailto:tswan-lst@tangent.ics.olemiss.edu>Thomas Swan
>To: <mailto:CTE@Dal.Ca>DalTech - CTE ; <mailto:tswan@olemiss.edu>Thomas Swan
>Cc: <mailto:pgsql-sql@postgresql.org>Pgsql-sql
>Sent: Tuesday, August 15, 2000 1:43 PM
>Subject: Re: [SQL] Functions with Null Arguments?
>
>At 10:12 AM 8/15/2000, DalTech - CTE wrote:
>>I would try using COALESCE(list) which will return the first non-NULL in 
>>the list.
>That worked! Thanks!
>
>The second part is why does postgresql not evaluate the function if one of 
>the arguments is null?

I'm using version 7.0.2 (x86 Linux)

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Functions with Null Arguments?
  2000-08-15 06:18 Functions with Null Arguments? Thomas Swan <tswan@olemiss.edu>
@ 2000-08-15 19:24 ` Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
  2 siblings, 0 replies; 5+ messages in thread

From: Jesus Aneiros @ 2000-08-15 19:24 UTC (permalink / raw)
  To: Thomas Swan <tswan@olemiss.edu>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-08-15 19:24 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-15 06:18 Functions with Null Arguments? Thomas Swan <tswan@olemiss.edu>
2000-08-15 12:12 ` DalTech - CTE <CTE@Dal.Ca>
2000-08-15 16:43 ` Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
2000-08-15 18:28   ` Thomas Swan <tswan-lst@tangent.ics.olemiss.edu>
2000-08-15 19:24 ` Jesus Aneiros <aneiros@jagua.cfg.sld.cu>

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