Re: Assert single row returning SQL-standard functions

On Fri, Aug 29, 2025, at 18:17, Pavel Stehule wrote:
>>
>> I am not too serious now, I am just playing (and I remember this discussion many times). We can "theoretically" introduce new keyword `EXACT`, that can specify so any DML or SELECT can process or returns just one row (or with other clause zero rows)
>>
>> EXACT ONE SELECT id FROM tab WHERE id = 1;
>> EXACT ONE UPDATE ...
>> EXACT ONE DELETE ...
>> EXACT ONE OR NONE SELECT ...
>
> or
>
> EXACT NONE SELECT ...

That would work, but I think I prefer CHECK DIAGNOSTICS (ROW_COUNT = 1),
feels a bit more SQL-idiomatic, since there seems to already be a
ROW_COUNT, and there is the concept of DIAGNOSTICS already, and CHECK
feels natural.

I can also imagine ROW_COUNT with other values than 1 could be useful,
e.g. ROW_COUNT = 2 to enforce inserting two transactions in a
double-entry bookkeeping system.

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.

/Joel