Re: [HACKERS] string_to_array with empty input

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> Or we could stick to the current behavior and say "use COALESCE() to
>> resolve the ambiguity, if you need to".

> Steve has a point that leaving it as-is leaves it as impossible to
> tell the difference between string_to_array(NULL, ',') and
> string_to_array('', ','). The former properly handles an unknown
> value, while the latter, where '' is a known value, seems weird to be
> returning NULL.

Yeah, COALESCE is an abuse of a convenient notation, which will fall
over if you also want NULL to yield NULL. A correct fix
outside-the-function would look more like

case when str = '' then '{}'::text[] else string_to_array(str, ',') end

which should correctly yield NULL for NULL input and an empty array
for empty input. Similarly, if someone wanted to force the
single-empty-string result, they should do

case when str = '' then '{""}'::text[] else string_to_array(str, ',') end

which also still yields NULL if str is NULL.

Right at the moment, if we stick with the historical definition
of the function, *both* camps have to write out their choice of
the above. Seems like this is the worst of all possible worlds.
We should probably pick one or the other.

regards, tom lane