Re: Problem with NULL value in arguments of server-side functions ( plpgsql )

In 7.0 and earlier, if any argument is NULL they are all treated as
NULL because there was only one flag to say whether an input was null.
In 7.1 this whole thing has been overhauled (yay tom!).

Right now there isn't a good workaround (you could possibly work around
by using coalesce and checking parameters inside, but that's pretty ugly)
other than waiting for 7.1 (or moving to CVS).

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Tue, 3 Oct 2000, Serg King wrote:

> Hi All,
> I'm using Postgres for the first time and have this problem:
> when I use NULL as the value of one of the parameters in my stored
> procedure, all the values of all the parameters in body of procedure will
> cleared to NULL too.
> (I have Postgres 7.0.2 on Linux RedHat 6.2 )
>
> The simple example:
>
> create function test(int4, char(10), date) returns int4 as '
> BEGIN
> INSERT INTO testtable VALUES ($1, $2, $3);
> return 0; /* only for the possibility to use SELECT for this function calls
> */
> END;
> ' language 'plpgsql';
>
> Try to use a NULL value when calling this function and you will see NULL
> values for all fields in the table:
>
> >SELECT test(1,'test', 'now'); - it works OK
> >SELECT test(1, NULL, 'now'); - and it has trouble:
> - after that see in testtable
> >SELECT * from testtable
> - and result is:
> +----+------+-----------+
> | t1 | t2 | t3 |
> +----+------+-----------+
> | 1 | test | 10/2/00 | - it's OK
> | | | | - but here all values are NULL!
> +----+------+-----------+
>
> My question is:
> Is it a bug in PG? Is there any solution?
> It is very important because I need to use NULL values.
> Thanks a lot before,
> Serge