Michael Glaesemann wrote:
>
> On Nov 24, 2005, at 21:21 , Marcus Engene wrote:
>
> > When we're having an alias discussion, I'd really like to see NVL
> > in postgres. Not because of porting from oracle as much as just
> > spelling that without the reference manual is completely impossible.
>
> NVL: what a very unfortunate spelling. (NULL VaLue? NULL Valued
> Logic? Named Very Loosely? Someone help me here :) ) AFAICT, COALESCE
> is SQL standard, while NVL isn't. I think an index entry might be a
> good idea.
Agreed, documentation patch applied to HEAD and 8.1.X.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.295
diff -c -c -r1.295 func.sgml
*** doc/src/sgml/func.sgml 19 Nov 2005 19:44:54 -0000 1.295
--- doc/src/sgml/func.sgml 28 Nov 2005 23:17:42 -0000
***************
*** 7227,7232 ****
--- 7227,7240 ----
<primary>COALESCE</primary>
</indexterm>
+ <indexterm>
+ <primary>NVL</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>IFNULL</primary>
+ </indexterm>
+
<synopsis>
<function>COALESCE</function>(<replaceable>value</replaceable> <optional>, ...</optional>)
</synopsis>
***************
*** 7234,7242 ****
<para>
The <function>COALESCE</function> function returns the first of its
arguments that is not null. Null is returned only if all arguments
! are null. This is often useful to substitute a
! default value for null values when data is retrieved for display,
! for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
--- 7242,7249 ----
<para>
The <function>COALESCE</function> function returns the first of its
arguments that is not null. Null is returned only if all arguments
! are null. It is often used to substitute a default value for
! null values when data is retrieved for display, for example:
<programlisting>
SELECT COALESCE(description, short_description, '(none)') ...
</programlisting>
***************
*** 7246,7252 ****
Like a <token>CASE</token> expression, <function>COALESCE</function> will
not evaluate arguments that are not needed to determine the result;
that is, arguments to the right of the first non-null argument are
! not evaluated.
</para>
</sect2>
--- 7253,7261 ----
Like a <token>CASE</token> expression, <function>COALESCE</function> will
not evaluate arguments that are not needed to determine the result;
that is, arguments to the right of the first non-null argument are
! not evaluated. This SQL-standard function provides capabilities similar
! to <function>NVL</> and <function>IFNULL</>, which are used in some other
! database systems.
</para>
</sect2>
***************
*** 7262,7277 ****
</synopsis>
<para>
! The <function>NULLIF</function> function returns a null value if and only
! if <replaceable>value1</replaceable> and
! <replaceable>value2</replaceable> are equal. Otherwise it returns
! <replaceable>value1</replaceable>. This can be used to perform the
! inverse operation of the <function>COALESCE</function> example
! given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
</para>
</sect2>
--- 7271,7289 ----
</synopsis>
<para>
! The <function>NULLIF</function> function returns a null value if
! <replaceable>value1</replaceable> and <replaceable>value2</replaceable>
! are equal; otherwise it returns <replaceable>value1</replaceable>.
! This can be used to perform the inverse operation of the
! <function>COALESCE</function> example given above:
<programlisting>
SELECT NULLIF(value, '(none)') ...
</programlisting>
</para>
+ <para>
+ If <replaceable>value1</replaceable> is <literal>(none)</>, return a null,
+ otherwise return <replaceable>value1</replaceable>.
+ </para>
</sect2>