Horton

Re: Assert single row returning SQL-standard functions

Joel Jacobson <joel@compiler.org>

View thread
From: "Joel Jacobson" <joel@compiler.org>
To: "Pavel Stehule" <pavel.stehule@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-08-29T09:51:12Z
Lists: pgsql-hackers

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

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