pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@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 <joel@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.
