pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRE: [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>
1999-06-14 21:20 ` Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL Tom Lane <tgl@sss.pgh.pa.us>
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 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>
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