Re: Assert single row returning SQL-standard functions

Hi

pá 29. 8. 2025 v 16:38 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> 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
>
>
Probably there is no other solution

CREATE OR REPLACE FUNCTION check_count(bigint)
RETURNS int AS $$
BEGIN
IF $1 <> 1 THEN
RAISE EXCEPTION 'unexpected number of rows';
END IF;
RETURN $1;
END;
$$ LANGUAGE plpgsql;

(2025-08-29 18:07:28) postgres=# select check_count((select count(*) from
pg_class where oid = 'pg_class'::regclass));
┌─────────────┐
│ check_count │
╞═════════════╡
│ 1 │
└─────────────┘
(1 row)

But all is +/- variant of your design

How useful is checking row_count other than one?

I am not too serious now, I am just playing (and I remember this discussion
many times). We can "theoretically" introduce new keyword `EXACT`, that can
specify so any DML or SELECT can process or returns just one row (or with
other clause zero rows)

EXACT ONE SELECT id FROM tab WHERE id = 1;
EXACT ONE UPDATE ...
EXACT ONE DELETE ...
EXACT ONE OR NONE SELECT ...

/Joel
>
>
>