RE: [GENERAL] Two variable passed to PL/Function and on is NULL

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(at)ludwig(dot)ucl(dot)ac(dot)uk]
> Sent: Monday, June 14, 1999 10:50 AM
> To: Herouth Maoz; pgsql-general(at)postgreSQL(dot)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(at)ludwig(dot)ucl(dot)ac(dot)uk |
> +-------------------------+--------------------------------------+