Re: Assert single row returning SQL-standard functions

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

> 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;
>
>
maybe, but the question is a scope. It should to work everywhere, or just
inside SQL function - or just for last SQL command in SQL function?

/Joel
>