Обсуждение: [PATCH] Add error_on_null() to produce an error if the input is null

Dear fellow hackers,

This polymorphic function produces an error if the input value is null,
otherwise it returns the input value unchanged.

This need for this function originates from the discussion
"Assert single row returning SQL-standard functions" [1].

/Joel

[1] https://www.postgresql.org/message-id/flat/9233b657-696f-430f-9557-dc602a2b9e0e%40app.fastmail.com

On 30/08/2025 13:49, Joel Jacobson wrote:
> Dear fellow hackers,
>
> This polymorphic function produces an error if the input value is null,
> otherwise it returns the input value unchanged.


This only checks for the null value.  I don't think we need to change 
the function's name, but we do need to be more precise in the 
documentation that it checks for the null value and not values that are 
null.


Perhaps replacing "Produces an error if the input is null, and returns 
the input otherwise." with "Produces an error if the input is the null 
value, and returns the input otherwise."


Then also add ROW(NULL, NULL)) to the regressions tests showing that we 
are aware that that is the behavior for things that are null but are not 
the null value.

-- 

Vik Fearing

Vik Fearing <vik@postgresfriends.org> writes:
> On 30/08/2025 13:49, Joel Jacobson wrote:
>> This polymorphic function produces an error if the input value is null,
>> otherwise it returns the input value unchanged.

> This only checks for the null value.  I don't think we need to change 
> the function's name, but we do need to be more precise in the 
> documentation that it checks for the null value and not values that are 
> null.

Agreed.  You might be able to borrow some wording from the docs'
discussion of the IS [NOT] NULL construct, and/or IS DISTINCT FROM.

            regards, tom lane

On Sat, Aug 30, 2025, at 18:38, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> On 30/08/2025 13:49, Joel Jacobson wrote:
>>> This polymorphic function produces an error if the input value is null,
>>> otherwise it returns the input value unchanged.
>
>> This only checks for the null value.  I don't think we need to change 
>> the function's name, but we do need to be more precise in the 
>> documentation that it checks for the null value and not values that are 
>> null.
>
> Agreed.  You might be able to borrow some wording from the docs'
> discussion of the IS [NOT] NULL construct, and/or IS DISTINCT FROM.
>
>             regards, tom lane

Thanks for great feedback. New patch attached.

/Joel

Thanks for great feedback. New patch attached.

This new patch resolves all of my concerns and I think it is ready for committer.  Does it have a commitfest entry where I can make that official?

-- 

Vik Fearing

On Sun, Aug 31, 2025, at 15:54, Vik Fearing wrote:
> On 31/08/2025 10:04, Joel Jacobson wrote: 
>> Thanks for great feedback. New patch attached.
>
>
> This new patch resolves all of my concerns and I think it is ready for 
> committer.

Thanks for reviewing.

> Does it have a commitfest entry where I can make that 
> official?

Yes: https://commitfest.postgresql.org/patch/6017/

/Joel

Does it have a commitfest entry where I can make that 
official?

Yes: https://commitfest.postgresql.org/patch/6017/

Thank you.  Updated.

-- 

Vik Fearing

On Sun, Aug 31, 2025 at 04:16:42PM +0200, Vik Fearing wrote:
> Thank you.  Updated.

This one was marked as committer, so I am looking at it.

The original proposal can also be tracked in this message, where you
want to have a trick for SQL bodies to be able to detect if exactly
one row is returned:
https://www.postgresql.org/message-id/de94808d-ed58-4536-9e28-e79b09a534c7@app.fastmail.com

Why not if this simple solution works for you, using error_on_null()
as the function name.  I'll double-check the whole, probably tomorrow.
--
Michael

On Tue, Oct 21, 2025 at 05:16:32PM +0900, Michael Paquier wrote:
> Why not if this simple solution works for you, using error_on_null()
> as the function name.  I'll double-check the whole, probably tomorrow.

Applied after a couple of tweaks applied to the code (like added pg_*
to the source function name) and the docs.

And of course I forgot a catversion bump..
--
Michael