Re: Assert single row returning SQL-standard functions

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