pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE: [GENERAL] Two variable passed to PL/Function and on is NULL
2+ messages / 2 participants
[nested] [flat]

* RE: [GENERAL] Two variable passed to PL/Function and on is NULL
@ 1999-06-14 19:03  Michael J Davis <michael.j.davis@tvguide.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael J Davis @ 1999-06-14 19:03 UTC (permalink / raw)
  To: 'Stuart Rison' <stuart@ludwig.ucl.ac.uk>; Herouth Maoz <herouth@oumail.openu.ac.il>; pgsql-general@postgreSQL.org, "'pgqsl-interfaces@postgreSQL.org'" <pgqsl-interfaces@postgreSQL.org>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL
@ 1999-06-14 21:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michael J Davis <michael.j.davis@tvguide.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 1999-06-14 21:20 UTC (permalink / raw)
  To: Michael J Davis <michael.j.davis@tvguide.com>; +Cc: pgsql-general@postgresql.org, "'pgqsl-interfaces@postgreSQL.org'" <pgqsl-interfaces@postgresql.org>; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-06-14 21:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-14 19:03 RE: [GENERAL] Two variable passed to PL/Function and on is NULL Michael J Davis <michael.j.davis@tvguide.com>
1999-06-14 21:20 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox