Re: Assert single row returning SQL-standard functions

pá 29. 8. 2025 v 18:51 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

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

+1

Orafce introduces https://github.com/orafce/orafce:

PLUnit <https://github.com/orafce/orafce#plunit>

This unit contains some assert functions.

-

plunit.assert_true(bool [, varchar]) - Asserts that the condition is
true.
-

plunit.assert_false(bool [, varchar]) - Asserts that the condition is
false.
-

plunit.assert_null(anyelement [, varchar]) - Asserts that the actual is
null.
-

plunit.assert_not_null(anyelement [, varchar]) - Asserts that the actual
isn’t null.
-

plunit.assert_equals(anyelement, anyelement [, double precision] [,
varchar]) - Asserts that expected and actual are equal.
-

plunit.assert_not_equals(anyelement, anyelement [, double precision] [,
varchar]) - Asserts that expected and actual are equal.
-

plunit.fail([varchar]) - Fail can be used to cause a test procedure to
fail immediately using the supplied message.

and for your case some aggregate functions can be nice too like

count_one, count_zero, count_one_zero, count_number, ...

Regards

Pavel

>
> /Joel