Re: Assert single row returning SQL-standard functions

On Fri, Aug 29, 2025 at 1:02 PM Joel Jacobson <joel(at)compiler(dot)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(at)compiler(dot)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.