Re: string_to_array with an empty input string

2010/8/11 Greg Stark <gsstark(at)mit(dot)edu>:
> On Wed, Aug 11, 2010 at 12:37 AM, Peter Geoghegan
> <peter(dot)geoghegan86(at)gmail(dot)com> wrote:
>> On 10 August 2010 19:48, David E. Wheeler <david(at)kineticode(dot)com> wrote:
>>> On Aug 10, 2010, at 11:46 AM, Thom Brown wrote:
>>>
>>>> I, personally, would expect an empty array output given an empty
>>>> input, and a null output for a null input.
>>>
>>> +1
>>
>> Agreed. After all, the result isn't indeterminate - it's an empty
>> array. Some people might think that it's useful for the result to be
>> NULL, but they'd probably also think that it's useful for an empty
>> string to be NULL.
>>
>
> For what it's worth there are two reasonable return values for
> string_to_array(''). It could be [] or it could be ['']. There are
> applications where the former makes the most sense and there are
> applications where the latter makes the most sense.

you have a true. The safe solution is return NULL on empty string. But
this behave is pretty unpractical for all domains other than texts. On
numeric or date there are not possible described situation.

I have a two ideas, just ideas:

a) to create a text_to_array function as complement to string_to_array
function. This function is same as string_to_array, but empty string
can be a NULL. But I see it as too academical.

b) to create a functions "explode" for other than text domains. One
parameter can be a regtype of expected array (maybe element). Then we
can correctly to decide what is correct result for empty string, and
we can to safe a some memory/time because the result will not be a
short life text array but desired array.

explode('1,2,3,4,,5', ',', '', int[])

Similar function have to be implemented with parser and transformation
changes - so we can design this function more verbose if we want:

explode('1,2,3,4,5,,' TO int[] DELIMITER AS ',' NULL AS '')

delimiter and nullstr can be a optional.

c) do nothing and returns NULL for empty string :(

I like a variant b.

Regards

Pavel Stehule

>
> Ideally you really want string_to_array(array_to_string(x, ':'),':')
> to return x. There's no safe return value to pick for the cases where
> x=[''] and x=[] that will make this work.
>
> --
> greg
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>