Re: Assert single row returning SQL-standard functions

pá 29. 8. 2025 v 12:22 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Fri, Aug 29, 2025, at 12:12, Pavel Stehule wrote:
>
> >> Yes, maybe, do you mean something like below?
> >> CREATE OR REPLACE FUNCTION fx(_a int)
> >> RETURNS bool
> >> SET assert_single_row = true
> >> BEGIN ATOMIC
> >> SELECT id = _a FROM footab WHERE id = _a; END;
> >>
> >
> > maybe, but the question is a scope. It should to work everywhere, or
> > just inside SQL function - or just for last SQL command in SQL function?
>
> Yeah, good question. I can see a value in such a GUC for psql sessions,
> to prevent against accidentally updating/deleting more rows than
> intended, but that's more "rows affected" than "rows returned", so maybe
> not a good match? If the semantics rows affected for DML, then it would
> work for functions that returns VOID also, so maybe that's better.
>
> Thanks to your ideas and focus on trying to find a way to achieve this
> with what we already have, I came up with a trick to prevent against
> >1 rows for DML, which is to use a SETOF returning wrapper function,
> in combination with the RETURN (...) trick:
>
> CREATE TABLE footab (id INT);
> INSERT INTO footab (id) VALUES (1), (10), (10);
>
> CREATE FUNCTION _test_update(_a int)
> RETURNS SETOF int
> BEGIN ATOMIC
> UPDATE footab SET id = _a WHERE id = _a RETURNING id;
> END;
>
> CREATE FUNCTION test_update(_a int)
> RETURNS int
> RETURN (SELECT _test_update(_a));
>
> joel=# SELECT test_update(1);
> test_update
> -------------
> 1
> (1 row)
>
> joel=# SELECT test_update(10);
> ERROR: more than one row returned by a subquery used as an expression
> CONTEXT: SQL function "test_update" statement 1
>
> Could something like that work? If so, then with your NOT NULL flag idea
> we would have a solution!
>

another possibility is to use plpgsql and extra check of row_count. It will
be more verbose and maybe more intuitive.

the overhead of plpgsql is low and usually it is faster than non-inlined
sql. Dependencies can be generated from plpgsql_check dependency report

>
> /Joel
>