pá 29. 8. 2025 v 10:30 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> > > pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <j...@compiler.org> > napsal: > >> On Fri, Aug 29, 2025, at 09:25, Pavel Stehule wrote: >> > pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <j...@compiler.org> >> napsal: >> ...ideas on syntax... >> >> These were just the two first ideas on the top of my head, please share >> >> yours if you see a better way. >> >> >> >> To me, if we can solve this problem, it would mean a huge improvement >> in >> >> how I work with database functions in PostgreSQL, since I would then >> get >> >> the nice benefits of dependency tracking and a more declarative mapping >> >> of how all database objects are connected to functions. >> >> >> >> I hope we can solve it together somehow. >> > >> > It is a question if there is some benefit or necessity to allow NON >> > STRICT behaviour there, and maybe it can be better to generally check >> > if the result is not trimmed? >> >> Thanks Pavel for sharing interesting ideas, the best would of course be >> if we could solve the problem without a new feature. >> >> Can you please help me understand what you mean with checking if the >> result "not trimmed"? >> > > I thought so there can be check, so result returns 0 or 1 rows. > >> >> > Secondary question is a fact, so proposed behaviour effectively breaks >> > inlining (what can be a performance problem, although for 18+ less than >> > before). >> >> Good point, however, if the alternative is plpgsql and its INTO STRICT, >> then it won't be inlined either? I happily accept no inlining, if it means >> I get the assurance of the SQL-function returning exactly one row. >> >> > The requested behaviour can be forced by using subquery and RETURN >> > command - and if I remember some articles and books related to this >> > topic, then subselects was used instead INTO >> >> Only partly. The requested behavior in my case, is asserting exactly one >> returned row, for SELECT, UPDATE, INSERT and DELETE in SQL-functions. >> The RETURN (...) trick only seems to protect against >1 rows, >> but doesn't protect against 0 rows: >> >> CREATE TABLE footab (id INT); >> INSERT INTO footab (id) VALUES (1), (10), (10); >> >> CREATE OR REPLACE FUNCTION fx(_a int) >> RETURNS bool >> RETURN (SELECT id = _a FROM footab WHERE id = _a); >> >> joel=# SELECT fx(12345); >> fx >> ---- >> >> (1 row) >> >> 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; > >> > 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 plpgsql has extra_checks, so maybe introduction similar GUC should not be too bad idea Pavel > > > >> >> /Joel >> >
