pá 29. 8. 2025 v 16:38 odesílatel Joel Jacobson <joel@compiler.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)
