Re: Assert single row returning SQL-standard functions

David G. Johnston <david.g.johnston@gmail.com>

From: "David G. Johnston" <david.g.johnston@gmail.com>

To: Joel Jacobson <joel@compiler.org>

Cc: Pavel Stehule <pavel.stehule@gmail.com>, Vik Fearing <vik@postgresfriends.org>, pgsql-hackers <pgsql-hackers@postgresql.org>

Date: 2025-08-29T20:42:13Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Add error_on_null(), checking if the input is the null value
- 2b75c38b707a 19 (unreleased) landed
SQL-standard function body
- e717a9a18b2e 14.0 cited

On Fri, Aug 29, 2025 at 1:02 PM Joel Jacobson <joel@compiler.org> wrote:

> On Fri, Aug 29, 2025, at 19:27, Pavel Stehule wrote:
> > pá 29. 8. 2025 v 18:51 odesílatel Joel Jacobson <joel@compiler.org>
> napsal:
> >> In the meantime, maybe we want to add a catalog function
> >> nonnull(anyelement) -> anyelement that throws an error if the input is
> >> NULL? Seems like a function that could be useful in general.
> >> Attached a small patch that adds such a function.
> >
> > +1
>
> New version attached. Added docs and tests. Wasn't sure where to put the
> docs. It's kinda a comparison function, since we compare against NULL
> internally? I put the docs in func-comparison.sgml, please advise if
> some other file would be better.
>
>
I think the file location is fine but have an idea for where within the
file to place this: or rather a minor re-working of these so three variants
that do the same test aren't spread across the whole page.

         <replaceable>datatype</replaceable> <literal>IS NULL</literal>
         <returnvalue>boolean</returnvalue>
        </para>
+       <para role="func_signature">
+        <replaceable>datatype</replaceable> <literal>ISNULL</literal>
+        <returnvalue>boolean</returnvalue> (non-standard syntax)
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>nonnull</primary>
+        </indexterm>
+        <function>nonnull</function> ( <type>anyelement</type> )
+        <returnvalue>anyelement | error</returnvalue>
+       </para>
        <para>
-        Test whether value is null.
+        Test whether value is null.  The second form is legacy non-standard
+        syntax.  The function call form produces an error if the input is
null,
+        and returns the input otherwise.
        </para>
        <para>
         <literal>1.5 IS NULL</literal>
         <returnvalue>f</returnvalue>
+       </para>
+       <para>
+        <literal>null ISNULL</literal>
+        <returnvalue>t</returnvalue>
+       </para>
+       <para>
+        <literal>nonnull(42)</literal>
+        <returnvalue>42</returnvalue>
+       </para>
+       <para>
+        <literal>nonnull(null)</literal>
+        <returnvalue>does not return</returnvalue>
        </para></entry>
       </row>

(remove ISNULL entry, and do similar consolidation for NOTNULL)

I do have a concern regarding its treatment of composites/row-valued inputs
(i.e. is this considered IS NOT NULL or IS DISTINCT FROM NULL)

The subject of this thread also is only tangentially related to the patch
now.

David J.