Re: Assert single row returning SQL-standard functions

Hi

pá 29. 8. 2025 v 9:03 odesílatel Joel Jacobson <joel(at)compiler(dot)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

>
> /Joel
>
>
>