Re: Assert single row returning SQL-standard functions

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