Re: Assert single row returning SQL-standard functions - Mailing list pgsql-hackers
| From | David G. Johnston |
|---|---|
| Subject | Re: Assert single row returning SQL-standard functions |
| Date | |
| Msg-id | CAKFQuwYBgoQ6anvu6-bzWg3G-kF2S6p7L57cYz8p_rfVUCh2OQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Assert single row returning SQL-standard functions ("Joel Jacobson" <joel@compiler.org>) |
| Responses |
Re: Assert single row returning SQL-standard functions
|
| List | pgsql-hackers |
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>
<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.
pgsql-hackers by date: