On Fri, Aug 29, 2025, at 16:09, Vik Fearing wrote:
> 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.
+1
I think that would be very readable. The meaning of the syntax
should be obvious to someone who knows what a CHECK constraint is, and
the parenthesis make it extendable.
I note CHECK is also a reserved word in DB2, MySQL, Oracle, SQL Server
and Teradata.
In the meantime, while waiting for SQL-standardization,
I wonder if we can do better than the below as a work-around?
CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);
CREATE OR REPLACE FUNCTION assert_not_null(val anyelement)
RETURNS anyelement
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
IF val IS NULL THEN
RAISE EXCEPTION 'unexpected null or zero rows';
END IF;
RETURN val;
END;
$$;
CREATE OR REPLACE FUNCTION _test_update(_a int)
RETURNS SETOF int
BEGIN ATOMIC
UPDATE footab SET id = _a WHERE id = _a RETURNING id;
END;
CREATE OR REPLACE FUNCTION test_update(_a int)
RETURNS int
RETURN (SELECT assert_not_null((SELECT _test_update(_a))));
joel=# SELECT test_update(100);
ERROR: unexpected null or zero rows
CONTEXT: PL/pgSQL function assert_not_null(anyelement) line 4 at RAISE
SQL function "test_update" statement 1
/Joel
