RE: [GENERAL] Two variable passed to PL/Function and on is NULL - Mailing list pgsql-general

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              |
> +-------------------------+--------------------------------------+