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