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