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

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:

create function null1000( int4 ) returns int4 as '
DECLARE
the_arg alias for $1;
BEGIN
IF the_arg IS NULL THEN
RETURN 1000;
ELSE
RETURN the_arg;
END IF;
END;
' language 'plpgsql';

I tested it on the following table:

testing=> select * from test1;
nm
--
4
8

16
32

(6 rows)

And this is the result I got:

testing=> select null1000( nm ) from test1;
null1000
--------
4
8
1000
16
32
1000
(6 rows)

The problem arises if you try to pass a literal NULL to the function:

testing=> select null1000( NULL );
ERROR: typeidTypeRelid: Invalid type - oid = 0

This is because the NULL doesn't have a type of int4. I am not even sure
this is a bug. In any case, it should work alright for normal NULLS. In the
same vein, you can ask whether the arguments in your function are null and
return your boolean properly.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma