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
