On Tue, Mar 31, 2009 at 05:45:33PM +1100, Brendan Jurd wrote:
> On Tue, Mar 31, 2009 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Does anyone want to argue for keeping it the same? Or perhaps
> > argue that a zero-element array is a more sensible result than
> > a one-element array with one empty string? (It doesn't seem
> > like it to me, but maybe somebody thinks so.)
>
> Given this behaviour, I would argue for consistent treatment for a
> zero-length source string: it should return an array with one element,
> being the entire source string, whenever there is no string splitting
> to take place. And if the source string happens to be zero-length,
> then the return value would be as expected by the OP.
I'd agree with this as well, just to be verbose:
string_to_array(NULL,',') => NULL
string_to_array('',',') => {""}
string_to_array('a',',') => {a}
string_to_array('a,',',') => {a,""}
string_to_array('a,b',',') => {a,b}
However, I can see (nasty and hacky) reasons why the current behaviour
is there. You'd get the following error if this change was accepted:
string_to_array('',',')::INT[] => invalid input syntax for integer: ""
Which you don't get at the moment; although you do currently get it in
other common cases such as:
string_to_array('1,',',')::INT[]
If you want backwards compatible behaviour you could always bung a
NULLIF in there:
string_to_array(NULLIF('',''),',')::INT[] => NULL
To aid porting of code and general utility, I'd be tempted to add a pair
of functions like:
CREATE FUNCTION array_filter_blanks(TEXT[]) RETURNS TEXT[]
LANGUAGE SQL IMMUTABLE STRICT AS $$
ARRAY(SELECT s FROM unnest($1) AS s WHERE s <> '') $$;
CREATE FUNCTION array_nullif(ANYARRAY,ANYELEMENT) RETURNS ANYARRAY
LANGUAGE SQL IMMUTABLE AS $$
ARRAY(SELECT NULLIF(s,$2) FROM unnest($1) AS s) $$;
Although, this is obviously going above and beyond what you originally
asked for.
--
Sam http://samason.me.uk/
