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: "Vik Fearing" <vik@postgresfriends.org>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-08-29T16:51:30Z
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

Attachments

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