Assert single row returning SQL-standard functions

Hi

pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <joel@compiler.org> napsal:

Dear fellow hackers,

Background:

Commit e717a9a "SQL-standard function body" introduced support for
SQL-standard functions, which have two great benefits compared to
plpgsql functions:

*) Dependency tracking

*) Renaming of database objects seamless, thanks to function body being
parsed at function definition time and stored as expression nodes.

Problem:

I really wish I could use such functions more often, but a very common
pattern in my database functions is the need to ensure exactly one row
was returned by a statement, which is currently only achievable via
plpgsql and its INTO STRICT.

I think we just need a way to assert return of a single row per
function, since if needed per statement, we can could just create
separate SQL-functions for each such statement, and execute them
separately, from a single function, if multiple statements are needed
within a single function.

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.

To me, if we can solve this problem, it would mean a huge improvement in
how I work with database functions in PostgreSQL, since I would then get
the nice benefits of dependency tracking and a more declarative mapping
of how all database objects are connected to functions.

I hope we can solve it together somehow.

It is a question if there is some benefit or necessity to allow NON STRICT
behaviour there, and maybe it can be better to generally check if the
result is not trimmed?

Secondary question is a fact, so proposed behaviour effectively breaks
inlining (what can be a performance problem, although for 18+ less than
before).

The requested behaviour can be forced by using subquery and RETURN command
- and if I remember some articles and books related to this topic, then
subselects was used instead INTO

SET var = (SELECT col FROM tab WHERE id = x);

Instead SELECT col INTO var FROM tab WHERE id = x;

(2025-08-29 09:19:24) postgres=# CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool RETURN (SELECT id = _a FROM footab WHERE id = _a);
CREATE FUNCTION
(2025-08-29 09:19:31) postgres=# SELECT fx(1);
┌────┐
│ fx │
╞════╡
│ t │
└────┘
(1 row)

(2025-08-29 09:19:33) postgres=# SELECT fx(10);
ERROR: more than one row returned by a subquery used as an expression
CONTEXT: SQL function "fx" statement 1

Subquery cannot be used when there are more than one OUT argument

Regards

Pavel

On Fri, Aug 29, 2025, at 09:25, Pavel Stehule wrote:

pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <joel@compiler.org> napsal:

...ideas on syntax...

These were just the two first ideas on the top of my head, please share
yours if you see a better way.

To me, if we can solve this problem, it would mean a huge improvement in
how I work with database functions in PostgreSQL, since I would then get
the nice benefits of dependency tracking and a more declarative mapping
of how all database objects are connected to functions.

I hope we can solve it together somehow.

It is a question if there is some benefit or necessity to allow NON
STRICT behaviour there, and maybe it can be better to generally check
if the result is not trimmed?

Thanks Pavel for sharing interesting ideas, the best would of course be
if we could solve the problem without a new feature.

Can you please help me understand what you mean with checking if the
result "not trimmed"?

Secondary question is a fact, so proposed behaviour effectively breaks
inlining (what can be a performance problem, although for 18+ less than
before).

Good point, however, if the alternative is plpgsql and its INTO STRICT,
then it won't be inlined either? I happily accept no inlining, if it means
I get the assurance of the SQL-function returning exactly one row.

The requested behaviour can be forced by using subquery and RETURN
command - and if I remember some articles and books related to this
topic, then subselects was used instead INTO

Only partly. The requested behavior in my case, is asserting exactly one
returned row, for SELECT, UPDATE, INSERT and DELETE in SQL-functions.
The RETURN (...) trick only seems to protect against >1 rows,
but doesn't protect against 0 rows:

CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
RETURN (SELECT id = _a FROM footab WHERE id = _a);

joel=# SELECT fx(12345);
fx
----

(1 row)

Can we think of some SQL-standard function way to also prevent against 0 rows?

/Joel

pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Fri, Aug 29, 2025, at 09:25, Pavel Stehule wrote:

pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <joel@compiler.org>

napsal:
...ideas on syntax...

These were just the two first ideas on the top of my head, please share
yours if you see a better way.

To me, if we can solve this problem, it would mean a huge improvement in
how I work with database functions in PostgreSQL, since I would then get
the nice benefits of dependency tracking and a more declarative mapping
of how all database objects are connected to functions.

I hope we can solve it together somehow.

It is a question if there is some benefit or necessity to allow NON
STRICT behaviour there, and maybe it can be better to generally check
if the result is not trimmed?

Thanks Pavel for sharing interesting ideas, the best would of course be
if we could solve the problem without a new feature.

Can you please help me understand what you mean with checking if the
result "not trimmed"?

I thought so there can be check, so result returns 0 or 1 rows.

Secondary question is a fact, so proposed behaviour effectively breaks
inlining (what can be a performance problem, although for 18+ less than
before).

Good point, however, if the alternative is plpgsql and its INTO STRICT,
then it won't be inlined either? I happily accept no inlining, if it means
I get the assurance of the SQL-function returning exactly one row.

The requested behaviour can be forced by using subquery and RETURN
command - and if I remember some articles and books related to this
topic, then subselects was used instead INTO

Only partly. The requested behavior in my case, is asserting exactly one
returned row, for SELECT, UPDATE, INSERT and DELETE in SQL-functions.
The RETURN (...) trick only seems to protect against >1 rows,
but doesn't protect against 0 rows:

CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
RETURN (SELECT id = _a FROM footab WHERE id = _a);

joel=# SELECT fx(12345);
fx
----

(1 row)

Can we think of some SQL-standard function way to also prevent against 0
rows?

I am afraid there is not nothing. NULL is the correct result in SQL. SQL
allow to check ROW_COUNT by using GET DIAGNOSTICS commands and raising an
error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the result
can be checked on NOT NULL value.

On Fri, Aug 29, 2025, at 10:30, Pavel Stehule wrote:

pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@compiler.org> napsal:

Can we think of some SQL-standard function way to also prevent against 0 rows?

I am afraid there is not nothing. NULL is the correct result in SQL.
SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and
raising an error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the
result can be checked on NOT NULL value.

I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?

Regarding DML functions, could we make the RETURN () trick work somehow?

Here is a failed attempt:

CREATE OR REPLACE FUNCTION test_update(_a int)
RETURNS bool
RETURN (
WITH update_cte AS (
UPDATE footab SET id = _a WHERE footab.id = _a RETURNING footab.id
)
SELECT id FROM update_cte
);

ERROR: WITH clause containing a data-modifying statement must be at the top level
LINE 4: WITH update_cte AS (
^

I'm not sure if this is a standard requirement, or if it's just a PostgreSQL-specific limitation?

/Joel

pá 29. 8. 2025 v 10:30 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@compiler.org>
napsal:

On Fri, Aug 29, 2025, at 09:25, Pavel Stehule wrote:

pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <joel@compiler.org>

napsal:
...ideas on syntax...

These were just the two first ideas on the top of my head, please share
yours if you see a better way.

To me, if we can solve this problem, it would mean a huge improvement

in

how I work with database functions in PostgreSQL, since I would then

get

the nice benefits of dependency tracking and a more declarative mapping
of how all database objects are connected to functions.

I hope we can solve it together somehow.

It is a question if there is some benefit or necessity to allow NON
STRICT behaviour there, and maybe it can be better to generally check
if the result is not trimmed?

Thanks Pavel for sharing interesting ideas, the best would of course be
if we could solve the problem without a new feature.

Can you please help me understand what you mean with checking if the
result "not trimmed"?

I thought so there can be check, so result returns 0 or 1 rows.

Secondary question is a fact, so proposed behaviour effectively breaks
inlining (what can be a performance problem, although for 18+ less than
before).

Good point, however, if the alternative is plpgsql and its INTO STRICT,
then it won't be inlined either? I happily accept no inlining, if it means
I get the assurance of the SQL-function returning exactly one row.

The requested behaviour can be forced by using subquery and RETURN
command - and if I remember some articles and books related to this
topic, then subselects was used instead INTO

Only partly. The requested behavior in my case, is asserting exactly one
returned row, for SELECT, UPDATE, INSERT and DELETE in SQL-functions.
The RETURN (...) trick only seems to protect against >1 rows,
but doesn't protect against 0 rows:

CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
RETURN (SELECT id = _a FROM footab WHERE id = _a);

joel=# SELECT fx(12345);
fx
----

(1 row)

Can we think of some SQL-standard function way to also prevent against 0
rows?

If I remember - in this case, the standard can raise a warning NOT FOUND.
Against Postgres, the warnings can be handled in SQL/PSM - and you can
raise an error or you can ignore it.

it can looks like

BEGIN
DECLARE assert_error CONDITION;
DECLARE HANDLER FOR NOT FOUND SIGNAL assert_error;
RETURN (SELECT id FROM footab WHERE id = _a);
END;

I am afraid there is not nothing. NULL is the correct result in SQL. SQL
allow to check ROW_COUNT by using GET DIAGNOSTICS commands and raising an
error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the
result can be checked on NOT NULL value.

but again NOT NULL is maybe some different than you want

plpgsql has extra_checks, so maybe introduction similar GUC should not be
too bad idea

Pavel

On Fri, Aug 29, 2025, at 11:52, Pavel Stehule wrote:

Can we think of some SQL-standard function way to also prevent against 0 rows?

If I remember - in this case, the standard can raise a warning NOT
FOUND. Against Postgres, the warnings can be handled in SQL/PSM - and
you can raise an error or you can ignore it.

it can looks like

BEGIN
DECLARE assert_error CONDITION;
DECLARE HANDLER FOR NOT FOUND SIGNAL assert_error;
RETURN (SELECT id FROM footab WHERE id = _a);
END;

Thanks, that's interesting, even though it's SQL/PSM, maybe it can give us some inspiration.

I am afraid there is not nothing. NULL is the correct result in SQL. SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and raising an error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the result can be checked on NOT NULL value.

but again NOT NULL is maybe some different than you want

I think NOT NULL would be fine, since in combination with the RETURN (...) trick,
that would assert one row, since zero rows would violate NOT NULL.

The only limitation would be not being able to return a NULL value,
but that seems like an acceptable limitation at least for most use cases I can imagine.

Is like below how you imagine the syntax?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool NOT NULL
RETURN (SELECT id = _a FROM footab WHERE id = _a);

plpgsql has extra_checks, so maybe introduction similar GUC should not
be too bad idea

Yes, maybe, do you mean something like below?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
SET assert_single_row = true
BEGIN ATOMIC
SELECT id = _a FROM footab WHERE id = _a;
END;

/Joel

pá 29. 8. 2025 v 11:51 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Fri, Aug 29, 2025, at 10:30, Pavel Stehule wrote:

pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@compiler.org>

napsal:

Can we think of some SQL-standard function way to also prevent against

0 rows?

I am afraid there is not nothing. NULL is the correct result in SQL.
SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and
raising an error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the
result can be checked on NOT NULL value.

I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?

CREATE OR REPLACE FUNCTION foo() RETURNS int NOT NULL AS $$ SELECT 10 $$
LANGUAGE ...

Regarding DML functions, could we make the RETURN () trick work somehow?

Here is a failed attempt:

CREATE OR REPLACE FUNCTION test_update(_a int)
RETURNS bool
RETURN (
WITH update_cte AS (
UPDATE footab SET id = _a WHERE footab.id = _a RETURNING footab.id
)
SELECT id FROM update_cte
);

ERROR: WITH clause containing a data-modifying statement must be at the
top level
LINE 4: WITH update_cte AS (
^

I'm not sure if this is a standard requirement, or if it's just a
PostgreSQL-specific limitation?

I am not sure in this case - I think so this syntax is maybe proprietary -
so it is not defined in standard, I cannot remember for ANSI/SQL syntax now.

any limit related to "top level" is PostgreSQL related

pá 29. 8. 2025 v 12:05 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Fri, Aug 29, 2025, at 11:52, Pavel Stehule wrote:

Can we think of some SQL-standard function way to also prevent against

0 rows?

If I remember - in this case, the standard can raise a warning NOT
FOUND. Against Postgres, the warnings can be handled in SQL/PSM - and
you can raise an error or you can ignore it.

it can looks like

BEGIN
DECLARE assert_error CONDITION;
DECLARE HANDLER FOR NOT FOUND SIGNAL assert_error;
RETURN (SELECT id FROM footab WHERE id = _a);
END;

Thanks, that's interesting, even though it's SQL/PSM, maybe it can give us
some inspiration.

I am afraid there is not nothing. NULL is the correct result in SQL.

SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and raising
an error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the

result can be checked on NOT NULL value.

but again NOT NULL is maybe some different than you want

I think NOT NULL would be fine, since in combination with the RETURN (...)
trick,
that would assert one row, since zero rows would violate NOT NULL.

The only limitation would be not being able to return a NULL value,
but that seems like an acceptable limitation at least for most use cases I
can imagine.

Is like below how you imagine the syntax?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool NOT NULL
RETURN (SELECT id = _a FROM footab WHERE id = _a);

plpgsql has extra_checks, so maybe introduction similar GUC should not
be too bad idea

Yes, maybe, do you mean something like below?

CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
SET assert_single_row = true
BEGIN ATOMIC
SELECT id = _a FROM footab WHERE id = _a;

END;

maybe, but the question is a scope. It should to work everywhere, or just
inside SQL function - or just for last SQL command in SQL function?

/Joel

On Fri, Aug 29, 2025, at 12:12, Pavel Stehule wrote:

Yes, maybe, do you mean something like below?
CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
SET assert_single_row = true
BEGIN ATOMIC
SELECT id = _a FROM footab WHERE id = _a; END;

maybe, but the question is a scope. It should to work everywhere, or
just inside SQL function - or just for last SQL command in SQL function?

Yeah, good question. I can see a value in such a GUC for psql sessions,
to prevent against accidentally updating/deleting more rows than
intended, but that's more "rows affected" than "rows returned", so maybe
not a good match? If the semantics rows affected for DML, then it would
work for functions that returns VOID also, so maybe that's better.

Thanks to your ideas and focus on trying to find a way to achieve this
with what we already have, I came up with a trick to prevent against

1 rows for DML, which is to use a SETOF returning wrapper function,

in combination with the RETURN (...) trick:

CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);

CREATE FUNCTION _test_update(_a int)
RETURNS SETOF int
BEGIN ATOMIC
UPDATE footab SET id = _a WHERE id = _a RETURNING id;
END;

CREATE FUNCTION test_update(_a int)
RETURNS int
RETURN (SELECT _test_update(_a));

joel=# SELECT test_update(1);
test_update
-------------
1
(1 row)

joel=# SELECT test_update(10);
ERROR: more than one row returned by a subquery used as an expression
CONTEXT: SQL function "test_update" statement 1

Could something like that work? If so, then with your NOT NULL flag idea
we would have a solution!

/Joel

pá 29. 8. 2025 v 12:22 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Fri, Aug 29, 2025, at 12:12, Pavel Stehule wrote:

Yes, maybe, do you mean something like below?
CREATE OR REPLACE FUNCTION fx(_a int)
RETURNS bool
SET assert_single_row = true
BEGIN ATOMIC
SELECT id = _a FROM footab WHERE id = _a; END;

maybe, but the question is a scope. It should to work everywhere, or
just inside SQL function - or just for last SQL command in SQL function?

Yeah, good question. I can see a value in such a GUC for psql sessions,
to prevent against accidentally updating/deleting more rows than
intended, but that's more "rows affected" than "rows returned", so maybe
not a good match? If the semantics rows affected for DML, then it would
work for functions that returns VOID also, so maybe that's better.

Thanks to your ideas and focus on trying to find a way to achieve this
with what we already have, I came up with a trick to prevent against

1 rows for DML, which is to use a SETOF returning wrapper function,

in combination with the RETURN (...) trick:

CREATE TABLE footab (id INT);
INSERT INTO footab (id) VALUES (1), (10), (10);

CREATE FUNCTION _test_update(_a int)
RETURNS SETOF int
BEGIN ATOMIC
UPDATE footab SET id = _a WHERE id = _a RETURNING id;
END;

CREATE FUNCTION test_update(_a int)
RETURNS int
RETURN (SELECT _test_update(_a));

joel=# SELECT test_update(1);
test_update
-------------
1
(1 row)

joel=# SELECT test_update(10);
ERROR: more than one row returned by a subquery used as an expression
CONTEXT: SQL function "test_update" statement 1

Could something like that work? If so, then with your NOT NULL flag idea
we would have a solution!

another possibility is to use plpgsql and extra check of row_count. It will
be more verbose and maybe more intuitive.

the overhead of plpgsql is low and usually it is faster than non-inlined
sql. Dependencies can be generated from plpgsql_check dependency report

pá 29. 8. 2025 v 11:51 odesílatel Joel Jacobson <joel@compiler.org> napsal:

On Fri, Aug 29, 2025, at 10:30, Pavel Stehule wrote:

pá 29. 8. 2025 v 10:16 odesílatel Joel Jacobson <joel@compiler.org>

napsal:

Can we think of some SQL-standard function way to also prevent against

0 rows?

I am afraid there is not nothing. NULL is the correct result in SQL.
SQL allow to check ROW_COUNT by using GET DIAGNOSTICS commands and
raising an error when something is unexpected

I can imagine allowing the NOT NULL flag for functions, and then the
result can be checked on NOT NULL value.

I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?

Regarding DML functions, could we make the RETURN () trick work somehow?

Here is a failed attempt:

CREATE OR REPLACE FUNCTION test_update(_a int)
RETURNS bool
RETURN (
WITH update_cte AS (
UPDATE footab SET id = _a WHERE footab.id = _a RETURNING footab.id
)
SELECT id FROM update_cte
);

ERROR: WITH clause containing a data-modifying statement must be at the
top level
LINE 4: WITH update_cte AS (
^

I'm not sure if this is a standard requirement, or if it's just a
PostgreSQL-specific limitation?

ANSI/SQL syntax is (pipelined DML)

SELECT oldtbl.empno FROM OLD TABLE (DELETE FROM emp WHERE deptno = 2) AS oldtbl;
SELECT newtbl.empno FROM NEW TABLE (UPDATE emp SET salary = 0 WHERE
deptno = 2) AS newtbl;

On 29/08/2025 12:06, Pavel Stehule wrote:

I can imagine allowing the NOT NULL flag for functions, and then

the

result can be checked on NOT NULL value.

I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?

CREATE OR REPLACE FUNCTION foo() RETURNS int NOT NULL AS $$ SELECT 10
$$ LANGUAGE ...

What if the function is supposed to return NULL sometimes?  The point
here is not the value but the row count.

--

Vik Fearing

On Friday, August 29, 2025, Joel Jacobson <joel@compiler.org> wrote:

These were just the two first ideas on the top of my head, please share
yours if you see a better way.

I’d love to just add a new clause to select.

Select …
Require {exactly|{more|less}than} row_count

e.g., exactly 1; more than 0, less than 2

No reason to limit this capability to function call outputs especially
since we would like to retain inlining when possible.

For insert/delete/update either add it directly there too or at worse you
get it via returning into a CTE and attaching “require” to the parent query.

David J.

pá 29. 8. 2025 v 15:47 odesílatel Vik Fearing <vik@postgresfriends.org>
napsal:

On 29/08/2025 12:06, Pavel Stehule wrote:

I can imagine allowing the NOT NULL flag for functions, and then the

result can be checked on NOT NULL value.

I like the idea of a NOT NULL flag for functions.
What syntax could we image for that?

CREATE OR REPLACE FUNCTION foo() RETURNS int NOT NULL AS $$ SELECT 10 $$
LANGUAGE ...

What if the function is supposed to return NULL sometimes? The point here
is not the value but the row count.

yes, it doesn't 100% cover Joel's proposal. It can work for naturally NOT
NULL domains only.

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

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

Hi

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)

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

/Joel

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 ...

or

EXACT NONE SELECT ...

On Fri, Aug 29, 2025, at 18:17, Pavel Stehule wrote:

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 ...

or

EXACT NONE SELECT ...

That would work, but I think I prefer CHECK DIAGNOSTICS (ROW_COUNT = 1),
feels a bit more SQL-idiomatic, since there seems to already be a
ROW_COUNT, and there is the concept of DIAGNOSTICS already, and CHECK
feels natural.

I can also imagine ROW_COUNT with other values than 1 could be useful,
e.g. ROW_COUNT = 2 to enforce inserting two transactions in a
double-entry bookkeeping system.

In the meantime, maybe we want to add a catalog function
nonnull(anyelement) -> anyelement that throws an error if the input is
NULL? Seems like a function that could be useful in general.
Attached a small patch that adds such a function.

/Joel