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

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!

/Joel