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
