pá 29. 8. 2025 v 12:05 odesílatel Joel Jacobson <j...@compiler.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 >
