Re: Assert single row returning SQL-standard functions - Mailing list pgsql-hackers

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.

 

/Joel