From fde74c2f69bccabf3ff283d4d8df997f25820784 Mon Sep 17 00:00:00 2001
From: Joel Jacobson <joel@compiler.org>
Date: Fri, 29 Aug 2025 21:50:17 +0200
Subject: [PATCH] Add nonnull() function to reject null values

nonnull(anyelement) returns its input if not null, else raises an error.
This can be handy in SQL-standard functions to enforce that exactly one
row was returned, since the common RETURN (SELECT ...) trick only
protects against multiple rows but not against zero rows.
---
 doc/src/sgml/func/func-comparison.sgml       | 16 ++++++++++++++++
 src/backend/utils/adt/misc.c                 | 16 ++++++++++++++++
 src/include/catalog/pg_proc.dat              |  3 +++
 src/test/regress/expected/misc_functions.out | 11 +++++++++++
 src/test/regress/sql/misc_functions.sql      |  7 +++++++
 5 files changed, 53 insertions(+)

diff --git a/doc/src/sgml/func/func-comparison.sgml b/doc/src/sgml/func/func-comparison.sgml
index c1205983f8b..4930fd41635 100644
--- a/doc/src/sgml/func/func-comparison.sgml
+++ b/doc/src/sgml/func/func-comparison.sgml
@@ -615,6 +615,22 @@ SELECT NOT(ROW(table.*) IS NOT NULL) FROM TABLE; -- detect at least one null in
         <returnvalue>2</returnvalue>
        </para></entry>
       </row>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>nonnull</primary>
+        </indexterm>
+        <function>nonnull</function> ( <type>anyelement</type> )
+        <returnvalue>anyelement</returnvalue>
+       </para>
+       <para>
+        Returns the input value if it is not null; raises an error if null.
+       </para>
+       <para>
+        <literal>nonnull(42)</literal>
+        <returnvalue>42</returnvalue>
+       </para></entry>
+      </row>
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index 6c5e3438447..d9f9d98df70 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -1121,3 +1121,19 @@ any_value_transfn(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_DATUM(PG_GETARG_DATUM(0));
 }
+
+/*
+ * nonnull()
+ *	Ensure that the argument is not NULL
+ *	If NULL, raise an error; otherwise return the value unchanged
+ */
+Datum
+nonnull(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));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..8b523612f6d 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 => 'ensure value is not null',
+  proname => 'nonnull', proisstrict => 'f', prorettype => 'anyelement',
+  proargtypes => 'anyelement', prosrc => 'nonnull' },
 
 { 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..d7378e19c59 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -178,6 +178,17 @@ LINE 1: SELECT num_nulls();
                ^
 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 --
+-- nonnull()
+--
+SELECT nonnull(1);
+ nonnull 
+---------
+       1
+(1 row)
+
+SELECT nonnull(NULL::int);
+ERROR:  null value not allowed
+--
 -- canonicalize_path()
 --
 CREATE FUNCTION test_canonicalize_path(text)
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 23792c4132a..d387db15568 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -77,6 +77,13 @@ SELECT num_nulls(VARIADIC '{}'::int[]);
 SELECT num_nonnulls();
 SELECT num_nulls();
 
+--
+-- nonnull()
+--
+
+SELECT nonnull(1);
+SELECT nonnull(NULL::int);
+
 --
 -- canonicalize_path()
 --
-- 
2.50.1