Re: Assert single row returning SQL-standard functions

pá 29. 8. 2025 v 10:30 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> 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?
>>
>
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
>>
>