Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add error_on_null(), checking if the input is the null value

  2. SQL-standard function body

  1. Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T07:02:43Z

    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

  2. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T07:25:47Z

    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



>
> /Joel
>
>
>

  3. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T08:16:10Z

    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

  4. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T08:30:58Z

    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.




>
> /Joel
>

  5. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T09:51:12Z

    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

  6. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T09:52:25Z

    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


>
>
>
>>
>> /Joel
>>
>

  7. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T10:05:36Z

    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

  8. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T10:06:09Z

    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




> /Joel
>

  9. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T10:12:57Z

    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
>

  10. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T10:22:30Z

    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

  11. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T12:30:35Z

    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



>
> /Joel
>

  12. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T12:57:21Z

    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;



>
> /Joel
>

  13. Re: Assert single row returning SQL-standard functions

    Vik Fearing <vik@postgresfriends.org> — 2025-08-29T13:47:52Z

    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

  14. Re: Assert single row returning SQL-standard functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-08-29T14:07:39Z

    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.

  15. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T14:08:00Z

    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.


> --
>
> Vik Fearing
>

  16. Re: Assert single row returning SQL-standard functions

    Vik Fearing <vik@postgresfriends.org> — 2025-08-29T14:09:17Z

    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

  17. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T14:38:05Z

    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

  18. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T16:14:17Z

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

  19. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T16:17:13Z

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

>
>
>
> /Joel
>>
>>
>>

  20. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T16:51:30Z

    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

  21. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-29T17:27:58Z

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

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

+1

Orafce introduces https://github.com/orafce/orafce:

PLUnit <https://github.com/orafce/orafce#plunit>

This unit contains some assert functions.

   -

   plunit.assert_true(bool [, varchar]) - Asserts that the condition is
   true.
   -

   plunit.assert_false(bool [, varchar]) - Asserts that the condition is
   false.
   -

   plunit.assert_null(anyelement [, varchar]) - Asserts that the actual is
   null.
   -

   plunit.assert_not_null(anyelement [, varchar]) - Asserts that the actual
   isn’t null.
   -

   plunit.assert_equals(anyelement, anyelement [, double precision] [,
   varchar]) - Asserts that expected and actual are equal.
   -

   plunit.assert_not_equals(anyelement, anyelement [, double precision] [,
   varchar]) - Asserts that expected and actual are equal.
   -

   plunit.fail([varchar]) - Fail can be used to cause a test procedure to
   fail immediately using the supplied message.


and for your case some aggregate functions can be nice too like

count_one, count_zero, count_one_zero, count_number, ...

Regards

Pavel


>
> /Joel

  22. Re: Assert single row returning SQL-standard functions

    Merlin Moncure <mmoncure@gmail.com> — 2025-08-29T17:34:27Z

    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?

merlin

  23. Re: Assert single row returning SQL-standard functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-08-29T17:44:29Z

    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.


> Also, how do search_path interactions work in your understanding?
>
>
The query in the atomic sql function behaves no differently than an
equivalent view.  OIDs don't care about search_path.

David J.

  24. Re: Assert single row returning SQL-standard functions

    Merlin Moncure <mmoncure@gmail.com> — 2025-08-29T17:49:15Z

    On Fri, Aug 29, 2025 at 11:45 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

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

sure, it's off topic to the main question, but it was noted in the intro.

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

roger.

merlin

  25. Re: Assert single row returning SQL-standard functions

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-29T18:07:17Z

    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

  26. Re: Assert single row returning SQL-standard functions

    Merlin Moncure <mmoncure@gmail.com> — 2025-08-29T19:12:50Z

    On Fri, Aug 29, 2025 at 12:07 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

Right, thanks.  The veiled note was this behavior specifically is not
uniformly better, given that pl/pgsql can be utilized to work around it.  I
have a lot more questions regarding standards compatibility coming to
procedures, but not for this thread.

merlin


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

  27. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-29T20:01:18Z

    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.

> Orafce introduces https://github.com/orafce/orafce:
>
> PLUnit
>
>  <https://github.com/orafce/orafce#plunit>
> This unit contains some assert functions.

Nice, that looks like an impressive collection of lots of useful
functions, thanks, will check it out.

/Joel

  28. Re: Assert single row returning SQL-standard functions

    David G. Johnston <david.g.johnston@gmail.com> — 2025-08-29T20:42:13Z

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

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

         <replaceable>datatype</replaceable> <literal>IS NULL</literal>
         <returnvalue>boolean</returnvalue>
        </para>
+       <para role="func_signature">
+        <replaceable>datatype</replaceable> <literal>ISNULL</literal>
+        <returnvalue>boolean</returnvalue> (non-standard syntax)
+       </para>
+       <para role="func_signature">
+        <indexterm>
+         <primary>nonnull</primary>
+        </indexterm>
+        <function>nonnull</function> ( <type>anyelement</type> )
+        <returnvalue>anyelement | error</returnvalue>
+       </para>
        <para>
-        Test whether value is null.
+        Test whether value is null.  The second form is legacy non-standard
+        syntax.  The function call form produces an error if the input is
null,
+        and returns the input otherwise.
        </para>
        <para>
         <literal>1.5 IS NULL</literal>
         <returnvalue>f</returnvalue>
+       </para>
+       <para>
+        <literal>null ISNULL</literal>
+        <returnvalue>t</returnvalue>
+       </para>
+       <para>
+        <literal>nonnull(42)</literal>
+        <returnvalue>42</returnvalue>
+       </para>
+       <para>
+        <literal>nonnull(null)</literal>
+        <returnvalue>does not return</returnvalue>
        </para></entry>
       </row>

(remove ISNULL entry, and do similar consolidation for NOTNULL)

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)

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

David J.

  29. Re: Assert single row returning SQL-standard functions

    Joel Jacobson <joel@compiler.org> — 2025-08-30T07:46:21Z

    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

  30. Re: Assert single row returning SQL-standard functions

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-08-30T09:16:36Z

    so 30. 8. 2025 v 9:46 odesílatel Joel Jacobson <joel@compiler.org> napsal:

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

+1

Pavel


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