Re: Assert single row returning SQL-standard functions

On Fri, Aug 29, 2025, at 11:52, Pavel Stehule wrote:
>>> Can we think of some SQL-standard function way to also prevent against 0 rows?
>
> If I remember - in this case, the standard can raise a warning NOT
> FOUND. Against Postgres, the warnings can be handled in SQL/PSM - and
> you can raise an error or you can ignore it.
>
> it can looks like
>
> BEGIN
> DECLARE assert_error CONDITION;
> DECLARE HANDLER FOR NOT FOUND SIGNAL assert_error;
> RETURN (SELECT id FROM footab WHERE id = _a);
> END;

Thanks, that's interesting, even though it's SQL/PSM, maybe it can give us some inspiration.

>> I am afraid there is not nothing. NULL is the correct result in SQL. SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and raising an error when something is unexpected
>>
>> I can imagine allowing the NOT NULL flag for functions, and then the result can be checked on NOT NULL value.
>
> but again NOT NULL is maybe some different than you want

I think NOT NULL would be fine, since in combination with the RETURN (...) trick,
that would assert one row, since zero rows would violate NOT NULL.

The only limitation would be not being able to return a NULL value,
but that seems like an acceptable limitation at least for most use cases I can imagine.

Is like below how you imagine the syntax?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool NOT NULL
RETURN (SELECT id = _a FROM footab WHERE id = _a);

> plpgsql has extra_checks, so maybe introduction similar GUC should not
> be too bad idea

Yes, maybe, do you mean something like below?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
SET assert_single_row = true
BEGIN ATOMIC
SELECT id = _a FROM footab WHERE id = _a;
END;

/Joel