pá 29. 8. 2025 v 12:22 odesílatel Joel Jacobson <j...@compiler.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 >
