pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael J Davis <michael.j.davis@tvguide.com>
To: 'Stuart Rison' <stuart@ludwig.ucl.ac.uk>
To: Herouth Maoz <herouth@oumail.openu.ac.il>
To: pgsql-general@postgreSQL.org, "'pgqsl-interfaces@postgreSQL.org'" <pgqsl-interfaces@postgreSQL.org>
To: 'pgsql-sql@postgreSQL.org' <pgsql-sql@postgreSQL.org>
Subject: RE: [GENERAL] Two variable passed to PL/Function and on is NULL
Date: Mon, 14 Jun 1999 14:03:48 -0500
Message-ID: <93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com> (raw)

Create an nz(int4) or nvl(int4) function that returns 0 if $1 is null.  You
could also create nz(text) that returns '' when $1 is null.  Then do:

	select my_function(nz(value1), nz(value2));

This has worked for me.

> -----Original Message-----
> From:	Stuart Rison [SMTP:stuart@ludwig.ucl.ac.uk]
> Sent:	Monday, June 14, 1999 10:50 AM
> To:	Herouth Maoz; pgsql-general@postgreSQL.org
> Subject:	Re: [GENERAL] Two variable passed to PL/Function and on is
> NULL
> 
> Hi Herouth,
> 
> >At 15:44 +0300 on 14/06/1999, Stuart Rison wrote:
> >
> >
> >> i) Is it the case that if you pass to variables to a postgres function
> and
> >> one is NULL, the function cannot tell which one?
> >> ii) Is there a workaround of some kind (in particular in pl/pgsql)?
> >> [Currently I am copying the table into a temp table and updating all
> NULL
> >> values to a token value.]
> >
> >I think you will do alright in pl/pgsql. Take for example the following
> >function which returns 1000 when its argument is null:
> >
> 
> <snip - a function that works fine but take only one argument>
> 
> The problem only occur when you have a function which takes more than one
> argument.
> 
> e.g.
> 
> create function null1000(int4,int4) returns int4 as '
> DECLARE
>   first_arg alias for $1;
>   second_arg alias for $2;
> BEGIN
>         IF first_arg IS NULL THEN
>                 RETURN 1000;
>   ELSE
>                 RETURN first_arg;
>         END IF;
> END;
> '
> language 'plpgsql';
> 
> cgh=> select * from test1;
>  nm|nm_two
> ---+------
>   4|
>   8|
>    |
>  16|
>  32|
>  64|     6
> 128|     7
> 256|     8
> 512|     9
>    |    10
> (10 rows)
> 
> cgh=> select nm,nm_two,null1000(nm,nm_two) from test1;
>  nm|nm_two|null1000
> ---+------+--------
>   4|      |    1000
>   8|      |    1000
>    |      |    1000
>  16|      |    1000
>  32|      |    1000
>  64|     6|      64
> 128|     7|     128
> 256|     8|     256
> 512|     9|     512
>    |    10|    1000
> (10 rows)
> 
> as soon a NULL is passed as an argument, both values are treated as NULL
> by
> the function.  I think the problem occurs before you even enter the
> function itself; in other words, both values become NULL 'internally' and
> not within the function so you can't trap them in the function itself with
> 'IS NULL' checks.
> 
> I think this is a know issue with 6.4 but there was some suggestion it may
> have been corrected in 6.5 (fact, fiction?)
> 
> furthermore, I'm unlikely to be moving to 6.5 in the near future so has
> anyone got a workaround?
> 
> cheers,
> 
> S.
> 
> +-------------------------+--------------------------------------+
> | Stuart Rison            | Ludwig Institute for Cancer Research |
> +-------------------------+ 91 Riding House Street               |
> | Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
> | Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
> +-------------------------+--------------------------------------+



view thread (2+ messages)  latest in thread

Message-ID: <93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com>
Permalink:  ../93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com/
Also on:    postgresql.org/message-id/93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com

 · 

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: michael.j.davis@tvguide.com, stuart@ludwig.ucl.ac.uk, herouth@oumail.openu.ac.il, pgqsl-interfaces@postgreSQL.org
  Subject: Re: [GENERAL] Two variable passed to PL/Function and on is NULL
  In-Reply-To: <93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com>
  References: <93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com>

* 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