Re: Assert single row returning SQL-standard functions

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