Thread
-
AW: [HACKERS] isnull() or is it?t
Zeugswetter Andreas <andreas.zeugswetter@telecom.at> — 1998-12-09T10:29:32Z
> 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
-
Re: AW: [HACKERS] isnull() or is it?t
H.Lefebvre <hlefebvre@easynet.fr> — 1998-12-09T14:56:12Z
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
-
Re: AW: [HACKERS] isnull() or is it?t
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-12-09T15:07:23Z
> > > 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, Pennsylvania 19026
-
Re: AW: [HACKERS] isnull() or is it?t
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-12-09T15:10:07Z
> 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, Pennsylvania 19026
-
Re: AW: [HACKERS] isnull() or is it?t
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-12-09T15:21:53Z
> 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 -
Re: AW: [HACKERS] isnull() or is it?t
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-12-09T15:47:08Z
> > 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, Pennsylvania 19026
-
Re: AW: [HACKERS] isnull() or is it?t
Vince Vielhaber <vev@michvhf.com> — 1998-12-09T16:16:42Z
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 entitled to lecture me about bloat than the federal government" -- Tony Snow ========================================================================== -
Re: AW: [HACKERS] isnull() or is it?t
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-12-09T18:17:21Z
> 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 -
Re[2]: AW: [HACKERS] isnull() or is it?t
jose' soares <sferac@bo.nettuno.it> — 1998-12-10T13:41:17Z
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. -
Re: AW: [HACKERS] isnull() or is it?t
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-12-10T16:45:08Z
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 -
Re: Re[2]: AW: [HACKERS] isnull() or is it?t
Jan Wieck <jwieck@debis.com> — 1998-12-10T20:26:41Z
> > 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) #