Re: Assert single row returning SQL-standard functions

[Vik Fearing]

On 29/08/2025 09:02, Joel Jacobson wrote:
Ideas on possible solutions:

How about piggy-backing on the CREATE FUNCTION's existing ROWS
parameter, and reusing the [ ENFORCED | NOT ENFORCED ] terminology we
have for constraints? We would need to lift the current restriction that
it is only allowed when the function return a set.

CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
ROWS 1 ENFORCED
BEGIN ATOMIC
     UPDATE foo SET a = _a WHERE b = _b RETURNING TRUE;
END;

CREATE FUNCTION test_select(_b int)
RETURNS INT
ROWS 1 ENFORCED
BEGIN ATOMIC
     SELECT a FROM foo WHERE b = _b;
END;

Alternatively, maybe we could set a per-function GUC,
e.g. SET assert_single_row = true?

CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
SET assert_single_row = true
BEGIN ATOMIC
     UPDATE foo SET a = _a WHERE b = _b RETURNING TRUE;
END;

CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
SET assert_single_row = true
BEGIN ATOMIC
     SELECT a FROM foo WHERE b = _b;
END;

INSERT and DELETE should be supported as well of course.

These were just the two first ideas on the top of my head, please share
yours if you see a better way.


The implementation is *supposed* to track several things for a query.  I 
am not sure PostgreSQL does this accurately or not.


The information is available through the GET DIAGNOSTICS command which 
postgres does not support (yet?).


So I might suggest something like:


    SELECT a
    FROM foo
    WHERE b = $1
    CHECK DIAGNOSTICS (ROW_COUNT = 1)


and


    UPDATE foo
    SET a = $1
    WHERE b = $2
    CHECK DIAGNOSTICS (ROW_COUNT = 1)


etc.


CHECK is already a reserved word in both postgres and the standard.

--

Vik Fearing