Re: Assert single row returning SQL-standard functions

On Fri, Aug 29, 2025, at 22:42, David G. Johnston wrote:
> 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.
>
...docs diff...

I like the idea of merging "IS NULL" with "ISNULL" and "IS NOT NULL"
with "NOTNULL", to make it clear they are the same thing but with
standard/non-standard syntax.

Not so sure about moving nonnull() from Table 9.3. Comparison Functions
up to Table 9.2. Comparison Predicates, since it's a function, so 9.3
feels more suitable.

> 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)

I think the semantics for the new function should be to error-on-null,
where the input strictly needs to be NULL to get an error, since then
it's possible to use such function for the assert single row use-case
even for functions that returns table / setof.

I do share your concern due to the current naming of the function
though.

How about renaming it to error_on_null(anyelement) -> anyelement
instead?

That way, we avoid the ambiguity coming from what "nonnull" would mean,
since it's only NULL that IS NULL.

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

Yeah, I think we should start a new thread for the patch, but holding onto
that until we've worked out what the function should be named and
what semantics we think it should have.

/Joel