Add error_on_null(), checking if the input is the null value

This polymorphic function produces an error if the input value is
detected as being the null value; otherwise it returns the input value
unchanged.

This function can for example become handy in SQL function bodies, to
enforce that exactly one row was returned.

Author: Joel Jacobson <joel@compiler.org>
Reviewed-by: Vik Fearing <vik@postgresfriends.org>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://postgr.es/m/ece8c6d1-2ab1-45d5-ba12-8dec96fc8886@app.fastmail.com
Discussion: https://postgr.es/m/de94808d-ed58-4536-9e28-e79b09a534c7@app.fastmail.com

1 files changed, 22 insertions, 0 deletions

diff --git a/doc/src/sgml/func/func-comparison.sgml b/doc/src/sgml/func/func-comparison.sgml
index c1205983f8b..ecb1d89463a 100644
--- a/doc/src/sgml/func/func-comparison.sgml
+++ b/doc/src/sgml/func/func-comparison.sgml
@@ -602,6 +602,28 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
+         <primary>error_on_null</primary>
+        </indexterm>
+        <function>error_on_null</function> ( <type>anyelement</type> )
+        <returnvalue>anyelement</returnvalue>
+       </para>
+       <para>
+        Checks if the input is the null value, generating an error if so;
+        otherwise, returns the input.
+       </para>
+       <para>
+        <literal>error_on_null(42)</literal>
+        <returnvalue>42</returnvalue>
+       </para>
+       <para>
+        <literal>error_on_null(row(null,null))</literal>
+        <returnvalue>(,)</returnvalue>
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
          <primary>num_nonnulls</primary>
         </indexterm>
         <function>num_nonnulls</function> ( <literal>VARIADIC</literal> <type>"any"</type> )