Re: BUG #18715: replace() function silently fails if 3rd argument is null

Nowhere (that I can see) does any documentation "define" that replace
returns null on null input to arg 3. Nor is it obvious that any "strict"
application of any principle should have it return null.

The documentation says that if arg 2 occurs in arg 1, it is replaced with
arg 3. Replacing text with null is problematic, but no problem arises if
there is nothing to be done.

Who exactly benefits from a function that fails to return the sensible
result that most clearly is available?

On Tue, 19 Nov 2024 at 21:08, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tue, Nov 19, 2024 at 5:52 AM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18715
>> Logged by: Chris
>> Email address: xpusostomos(at)gmail(dot)com
>> PostgreSQL version: 16.5
>> Operating system: Linux
>> Description:
>>
>> OK, but now imagine that at runtime the 3rd argument is null... and we
>> expect it to be null because that variable is not used in that tuple:
>> replace('abcdef', '${m}', null) => null
>>
>
> This is not a bug. Replace is defined to return null on null input (i.e.,
> strict) and that is the behavior you are seeing.
>
> Use Coalesce to convert your null into an empty string.
>
> David J.
>