Thread: [GENERAL] Two variable passed to PL/Function and on is NULL
Dear All, I have written a PL/pgSQL fucntion which takes two arguments and returns a boolean on the basis of a few (string) comparaisons. The function works fine as long as BOTH passed variables are non-NULL but behaves strangely if one of the variables is non-NULL. Looking at previous postings, I found: >Very observant of you. The postgres function system doesn't have a way >to tell which parameter is null, so the function just returns null. >I don't like it and we hope to get it changed for PGv6.5 (at least I >do). Now that example was with a 'c' function but presumably, function are handled the same way internally whatever the function language? SO my question are: 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.] regards, Stuart. +-------------------------+--------------------------------------+ | 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 | +-------------------------+--------------------------------------+
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
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 |
+-------------------------+--------------------------------------+
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 |
> +-------------------------+--------------------------------------+
Michael J Davis <michael.j.davis@tvguide.com> writes:
> 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.
Also, you can use the COALESCE() standard function to handle this sort
of thing: COALESCE(a,b,...) basically returns the first non-null value
in its argument list. So COALESCE(x,0) or COALESCE(x,'') would handle
the above requirements.
COALESCE is a shorthand form of a CASE expression, which allows for
even more general if-then-else calculations.
CASE is present but a tad buggy in 6.4 (IIRC, it fails if used in a
SELECT that joins more than one table). There are no known problems
with it in 6.5.
regards, tom lane