Thread: RE: [HACKERS] PL/pgSQL a great procedural language for PostgreSQL

It appears that when a function is called if any of the paramaters are
NULL all of the parameters are NULL.
try:drop function nvl(int, int);create function nvl(int, int) returns boolean as ' declare   nonullo alias as $1;
nulloalias as $2; begin  return (nonullo IS NULL) AND (nullo IS NULL); end;' language 'plpgsql';select nvl(i,0) from
a;
you should get:
nvl
---
t
t
f
t
(4 rows)



> Hi all,
> 
> ----I'm trying to understand this very interesting language PL/pgSQL
> thanks to Jan Wieck,
> ----finally we can trap NULL values as in:
> 
> -------and seems it returns only constant values:
> 
> drop function nvl(integer,integer);
> DROP
> create function nvl(integer,integer) returns integer as '
> declare
>         nonullo alias for $1;
>         nullo   ALIAS FOR $2;
> begin
>         if NONULLO then
>                 return NONULLO;
>         else
>                 return NULLO;
>         end if;
> end;' language 'plpgsql';
> CREATE
> 
> select nvl(i,0) from a;
>     nvl
> -------
> 
> 
> 2232767
> 
> (4 rows)
> 
>                      Jose'

>
> It appears that when a function is called if any of the paramaters are
> NULL all of the parameters are NULL.
> try:
>  drop function nvl(int, int);
>  create function nvl(int, int) returns boolean as '
>   declare
>     nonullo alias as $1;
>     nullo alias as $2;
>   begin
>    return (nonullo IS NULL) AND (nullo IS NULL);
>   end;' language 'plpgsql';
>  select nvl(i,0) from a;
> you should get:
> nvl
> ---
> t
> t
> f
> t
> (4 rows)

    Don't blame PL/pgSQL for that.  There is only one bool isNull
    pointer given to PL handlers. How should the PL handler know,
    which  of  the arguments are null then?  As I said on another
    thread, the function call interface needs to get  redesigned.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #