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? > > 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. > > /Joel >
