[PATCH] Add error_on_null() to produce an error if the input is null

From 5afb3144f8da393cd8a5248e3fb5debc40963293 Mon Sep 17 00:00:00 2001
From: Joel Jacobson <joel@compiler.org>
Date: Sat, 30 Aug 2025 12:13:35 +0200
Subject: [PATCH] Add error_on_null() to produce an error if the input is the
 null value

This polymorphic function produces an error if the input value is the
null value, otherwise it returns the input value unchanged.
---
 doc/src/sgml/func/func-comparison.sgml       | 24 ++++++++++++++++++++
 src/backend/utils/adt/misc.c                 | 15 ++++++++++++
 src/include/catalog/pg_proc.dat              |  3 +++
 src/test/regress/expected/misc_functions.out | 23 +++++++++++++++++++
 src/test/regress/sql/misc_functions.sql      |  9 ++++++++
 5 files changed, 74 insertions(+)

diff --git a/doc/src/sgml/func/func-comparison.sgml b/doc/src/sgml/func/func-comparison.sgml
index c1205983f8b..bbf1fa1f45f 100644
--- a/doc/src/sgml/func/func-comparison.sgml
+++ b/doc/src/sgml/func/func-comparison.sgml
@@ -631,6 +631,30 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         <returnvalue>1</returnvalue>
        </para></entry>
       </row>
+      <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 | error</returnvalue>
+       </para>
+       <para>
+        Produces an error if the input is the null value, and returns the input otherwise.
+       </para>
+       <para>
+        <literal>error_on_null(42)</literal>
+        <returnvalue>42</returnvalue>
+       </para>
+       <para>
+        <literal>error_on_null(row(null,null))</literal>
+        <returnvalue>(,)</returnvalue>
+       </para>
+       <para>
+        <literal>error_on_null(null)</literal>
+        <returnvalue>does not return</returnvalue>
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 6c5e3438447..278b1ad0464 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -185,6 +185,21 @@ pg_num_nonnulls(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(nargs - nulls);
 }
 
+/*
+ * error_on_null()
+ *	Produces an error if the input is the null value, and returns the input otherwise
+ */
+Datum
+error_on_null(PG_FUNCTION_ARGS)
+{
+	if (PG_ARGISNULL(0))
+		ereport(ERROR,
+				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+				 errmsg("null value not allowed")));
+
+	PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+}
+
 
 /*
  * current_database()
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..c81fbb1f998 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12518,6 +12518,9 @@
 { oid => '6292', descr => 'aggregate transition function',
   proname => 'any_value_transfn', prorettype => 'anyelement',
   proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' },
+{ oid => '8488', descr => 'error if input is the null value; otherwise returns input unchanged',
+  proname => 'error_on_null', proisstrict => 'f', prorettype => 'anyelement',
+  proargtypes => 'anyelement', prosrc => 'error_on_null' },
 
 { oid => '6321', descr => 'list of available WAL summary files',
   proname => 'pg_available_wal_summaries', prorows => '100', proretset => 't',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index c3b2b9d8603..8c1e9c8114d 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -177,6 +177,29 @@ ERROR:  function num_nulls() does not exist
 LINE 1: SELECT num_nulls();
                ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+--
+-- error_on_null()
+--
+SELECT error_on_null(1);
+ error_on_null 
+---------------
+             1
+(1 row)
+
+SELECT error_on_null(NULL::int);
+ERROR:  null value not allowed
+SELECT error_on_null(ROW(1,NULL::int));
+ error_on_null 
+---------------
+ (1,)
+(1 row)
+
+SELECT error_on_null(ROW(NULL,NULL));
+ error_on_null 
+---------------
+ (,)
+(1 row)
+
 --
 -- canonicalize_path()
 --
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132a..4cc2f2aae0a 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -77,6 +77,15 @@ SELECT num_nulls(VARIADIC '{}'::int[]);
 SELECT num_nonnulls();
 SELECT num_nulls();
 
+--
+-- error_on_null()
+--
+
+SELECT error_on_null(1);
+SELECT error_on_null(NULL::int);
+SELECT error_on_null(ROW(1,NULL::int));
+SELECT error_on_null(ROW(NULL,NULL));
+
 --
 -- canonicalize_path()
 --
-- 
2.50.1