Re: patch: to_string, to_array functions

On 17 July 2010 02:15, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2010/7/16 Brendan Jurd <direvus@gmail.com>:
>> Regarding the behaviour of the third argument (null_string), I was a
>> little surprised by the results when I passed in a NULL.
>>
>
> I didn't thinking about NULL as separator before. Current behave isn't
> practical. When default separator is empty string, then NULL can be
> used as ignore NULLs - so it can emulate current string_to_array and
> array_to_string behave. It can be, because NULL can't be a separator
> ever.
>
> select to_string(array[1,2,3,null,5], ',') -> 1,2,3,,5
> select to_string(array[1,2,3,null,5], ',', null) -> 1,2,3,5
>
> maybe - next idea and maybe better - we can check NOT NULL for
> separator and to add other parameter with default = false -
> ignore_null
>
> select to_string(array[1,2,3,null,5], ',', ignore_null := true) -> 1,2,3,5
>
> what do you think?

I don't have any problem with null_string = NULL in to_string taking
the meaning "skip over NULL elements".  It's a slightly strange
outcome but it's more useful than returning NULL, and I do like that
it gives us a path to the current array_to_string() treatment even if
those functions are ultimately deprecated.  I think adding a fourth
keyword argument might be sacrificing a little too much convenience in
the calling convention.

As for to_array, null_string = NULL should mean that there is no
string which should result in a NULL element.  So I would be happy to
see the following set of behaviours:

to_string(array[1, 2, 3, 4, 5], ',', null) = '1,2,3,4,5'
to_string(array[1, 2, 3, null, 5], ',', null) = '1,2,3,5'
to_array('1,2,3,,5', ',', null) = '{1,2,3,"",5}'

Also, if we're going to make the function non-strict, we need to
consider how to respond when the user specifies NULL for the other
arguments.  If the field separator is NULL, bearing in mind that NULL
can't match any string, I would expect that to_array would return the
undivided string as a single array element, and that to_string would
throw an error:

to_array('1,2,3,4,5', null) = '{"1,2,3,4,5"}'
to_string(array[1,2,3,4,5], null) = ERROR: the field separator for
to_string may not be NULL

If the first argument is NULL for either function, I think it would be
reasonable to return NULL.  But I could be convinced that we should
throw an error in that case too.

Cheers,
BJ