Обсуждение: AW: [HACKERS] isnull() or is it?t
> isnull() is on our TODO list. It should be called NVL() from "Null VaLue" like in Oracle and Informix.isnull() would suggest a boolean return value to me. Andreas
Zeugswetter Andreas IZ5 wrote: > > > isnull() is on our TODO list. > > It should be called NVL() from "Null VaLue" like in Oracle and > Informix. > isnull() would suggest a boolean return value to me. > With SyBase, IsNull(X,Y) returns X if X is not null, and Y if X is null. Example: SELECT reference, name, IsNull(price, 0.00) FROM t_items reference name price ------------ --------------- ------- W95 Windows95 190.00 WNT400 Windows NT 4.0 490.00 LX Linux 0.00 rather than: SELECT reference, name, price, FROM t_items reference name price ------------ --------------- ------- W95 Windows95 190.00 WNT400 Windows NT 4.0 490.00 LX Linux NULL -- H.Lefebvre
> With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> if X is null.
Which is identical in behavior to the SQL92-defined function
COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
Postgres development tree, to be available in the next release. Should
we also have the less capable IsNull() available too? COALESCE() has the
nice feature that it takes an unlimited number of arguments, returning
the first non-null result.
- Tom
> > > isnull() is on our TODO list. > > It should be called NVL() from "Null VaLue" like in Oracle and > Informix. > isnull() would suggest a boolean return value to me. > > Andreas > > NVL added to TODO. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Zeugswetter Andreas IZ5 wrote: > > > > > isnull() is on our TODO list. > > > > It should be called NVL() from "Null VaLue" like in Oracle and > > Informix. > > isnull() would suggest a boolean return value to me. > > > > > With SyBase, IsNull(X,Y) returns X if X is not null, and Y > if X is null. We should support both isnull() and nvl(). On the TODO list now. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > With SyBase, IsNull(X,Y) returns X if X is not null, and Y > > if X is null. > > Which is identical in behavior to the SQL92-defined function > COALESCE(X,Y) (IsNull() is not in the standard). This is now in the > Postgres development tree, to be available in the next release. Should > we also have the less capable IsNull() available too? COALESCE() has the > nice feature that it takes an unlimited number of arguments, returning > the first non-null result. Oh, NVL and isnull are not standard? Then let's just use coalesce. I will remove them from the TODO list. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Wed, 9 Dec 1998, Bruce Momjian wrote:
> > > With SyBase, IsNull(X,Y) returns X if X is not null, and Y
> > > if X is null.
> >
> > Which is identical in behavior to the SQL92-defined function
> > COALESCE(X,Y) (IsNull() is not in the standard). This is now in the
> > Postgres development tree, to be available in the next release. Should
> > we also have the less capable IsNull() available too? COALESCE() has the
> > nice feature that it takes an unlimited number of arguments, returning
> > the first non-null result.
>
> Oh, NVL and isnull are not standard? Then let's just use coalesce. I
> will remove them from the TODO list.
Surprises me too. I was under the (incorrect) impression that isnull
was standard.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h>
TEAM-OS2 Online Searchable Campground Listings http://www.camping-usa.com "There is no outfit less
entitledto lecture me about bloat than the federal government" -- Tony Snow
==========================================================================
> Surprises me too. I was under the (incorrect) impression that isnull
> was standard.
If it is, my books don't bother mentioning it. It would be pretty easy
to implement, if it *is* standard, since it is just a subset of
COALESCE() behavior.
But I'm a bit worried about the recent trend toward coding in
non-standard extensions as *alternatives* to standards-based behavior
just because a specific product has it. I know it's convenient for some,
but there are *lots* of products which have these kinds of things, each
in a different way, and at some point we would collapse under the weight
of trying to be all things at once.
Not naming any names, but *$ products seem to be the worst violators of
the simplest standards. One might ascribe this to simple incompetence,
except for their no-longer-in-doubt agenda of subverting standards in a
so-far-successful drive for market dominance. For example, Access
produces code of the form val = NULL
as an equivalent to the SQL92-standard val IS NULL
Where my Date book has a sentence:
"It is not possible to specify NULL explicitly as an operand of a
conditional expression - e.g., 'WHERE X = NULL' is illegal."
We now have a parser which violates that, for the sake of Access
compatibility. Of course, we do support another extension to SQL92 which
violates another rule in the same area:
"It is not possible to specify NULL explicitly as a select-item -- e.g.,
'SELECT NULL' is illegal."
Oh well. otoh, perhaps some of this would be legal for SQL3, which has a
somewhat more developed concept of NULL...
- Tom
If it is interesting to someone, we can partially emulate COALESCE right now as: create function coalesce(integer) returns integer as 'declare nonullo alias for $1; begin if nonullo then return nonullo; else return 0; end if;end; ' language 'plpgsql'; CREATE select *,coalesce(comm) from emp where comm is null; ename|empno|job | hiredate|sal |comm|deptno|level| mgr|coalesce -----+-----+----------+----------+---------+----+------+-----+----+-------- BLAKE| 7698|MANAGER |1981-05-01|$2,850.00| | 30| 3|7782| 0 JONES| 7900|CLERK |1981-12-03|$950.00 | | 30| 2|7782| 0 CLARK| 7844|SALESMAN |1981-09-08|$1,500.00| | 10| 2|7839| 0 (3 rows) -Jose'- >> > > With SyBase, IsNull(X,Y) returns X if X is not null, and Y >> > > if X is null. >> > >> > Which is identical in behavior to the SQL92-defined function >> > COALESCE(X,Y) (IsNull() is not in the standard). This is now in the >> > Postgres development tree, to be available in the next release. Should >> > we also have the less capable IsNull() available too? COALESCE() has the >> > nice feature that it takes an unlimited number of arguments, returning >> > the first non-null result. >> >> Oh, NVL and isnull are not standard? Then let's just use coalesce. I >> will remove them from the TODO list. VV> Surprises me too. I was under the (incorrect) impression that isnull VV> was standard.
On a somewhat related topic, the development parser also now supports
the SQL92-standard NULLIF(), which behaves in an opposite sense to the
common IsNull() under discussion. That is, it tests the first argument
against the second, and then returns NULL if they are equal. Weird.
- Tom
>
> If it is interesting to someone, we can partially emulate COALESCE
> right now as:
>
> create function coalesce(integer) returns integer as
> 'declare
> nonullo alias for $1;
> begin
> if nonullo then
> return nonullo;
> else
> return 0;
> end if;
> end;
> ' language 'plpgsql';
> CREATE
Pardon, but you still misuse the fact, that PL/pgSQL's IF
expression is implicitly casted into a boolean. That's only
possible for integer values.
Please use
IF nonullo ISNULL THEN
RETURN 0;
ELSE
RETURN nonullo;
END IF;
instead, because this would work for other types (like text,
varchar etc.) too.
Since PL functions can be overloaded (like SQL functions), it
would be possible, but currently not that performant :-(, to
create such a function for all types required.
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) #