Обсуждение: Assert single row returning SQL-standard functions

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.

/Joel

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

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

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

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

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?

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;

/Joel

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

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

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

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

On Fri, Aug 29, 2025 at 1:03 AM Joel Jacobson <joel@compiler.org> wrote:

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

How does that work in practice?  for current SQL (not pl/pgsql) functions,  this will fail:

create function f() returns int as $$ create temp table i(i int); select * from i; $$ language sql; 

ERROR:  relation "i" does not exist

Also, how do search_path interactions work in your understanding?

On Fri, Aug 29, 2025 at 10:34 AM Merlin Moncure <mmoncure@gmail.com> wrote:

On Fri, Aug 29, 2025 at 1:03 AM Joel Jacobson <joel@compiler.org> wrote:

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

How does that work in practice?  for current SQL (not pl/pgsql) functions,  this will fail:

create function f() returns int as $$ create temp table i(i int); select * from i; $$ language sql; 

ERROR:  relation "i" does not exist

This example seems unrelated to the point being made.

The query in the atomic sql function behaves no differently than an equivalent view.  OIDs don't care about search_path.

Merlin Moncure <mmoncure@gmail.com> writes:
> How does that work in practice?  for current SQL (not pl/pgsql) functions,
> this will fail:

> create function f() returns int as $$ create temp table i(i int); select *
> from i; $$ language sql;
> ERROR:  relation "i" does not exist

Slightly off-topic: that example does actually work as of v18,
although you need to turn off check_function_bodies while
creating the function:

$ psql regression
psql (18beta3)
Type "help" for help.

regression=# create function f() returns int as $$ create temp table i(i int); select *
from i; $$ language sql;
ERROR:  relation "i" does not exist
LINE 2: from i; $$ language sql;
             ^
regression=# set check_function_bodies to off;
SET
regression=# create function f() returns int as $$ create temp table i(i int); select *
from i; $$ language sql;
CREATE FUNCTION
regression=# select f();
 f
---

(1 row)

regression=# \d i
               Table "pg_temp_70.i"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 i      | integer |           |          |


But David is correct that this is irrelevant to the case of
SQL-standard functions.  Everything mentioned in such a function has
to exist at function creation time, no exceptions.

There's a closely related complaint at [1], which I rather doubt
we're going to do anything about.

                        regards, tom lane

[1] https://www.postgresql.org/message-id/19034-de0857b4f94ec10c%40postgresql.org

On Fri, Aug 29, 2025, at 19:27, Pavel Stehule wrote:
> pá 29. 8. 2025 v 18:51 odesílatel Joel Jacobson <joel@compiler.org> napsal:
>> 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.
>
> +1

New version attached. Added docs and tests. Wasn't sure where to put the
docs. It's kinda a comparison function, since we compare against NULL
internally? I put the docs in func-comparison.sgml, please advise if
some other file would be better.

On Fri, Aug 29, 2025, at 22:42, David G. Johnston wrote:
> I think the file location is fine but have an idea for where within the
> file to place this: or rather a minor re-working of these so three
> variants that do the same test aren't spread across the whole page.
>
...docs diff...

I like the idea of merging "IS NULL" with "ISNULL" and "IS NOT NULL"
with "NOTNULL", to make it clear they are the same thing but with
standard/non-standard syntax.

Not so sure about moving nonnull() from Table 9.3. Comparison Functions
up to Table 9.2. Comparison Predicates, since it's a function, so 9.3
feels more suitable.

> I do have a concern regarding its treatment of composites/row-valued
> inputs (i.e. is this considered IS NOT NULL or IS DISTINCT FROM NULL)

I think the semantics for the new function should be to error-on-null,
where the input strictly needs to be NULL to get an error, since then
it's possible to use such function for the assert single row use-case
even for functions that returns table / setof.

I do share your concern due to the current naming of the function
though.

How about renaming it to error_on_null(anyelement) -> anyelement
instead?

That way, we avoid the ambiguity coming from what "nonnull" would mean,
since it's only NULL that IS NULL.

> The subject of this thread also is only tangentially related to the patch now.

Yeah, I think we should start a new thread for the patch, but holding onto
that until we've worked out what the function should be named and
what semantics we think it should have.

/Joel