Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] string_to_array with empty input

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] string_to_array with empty input
Date: 2009-03-31 14:42:56
Message-ID: 20090331144256.GB12225@frubble.xen.chris-lamb.co.uk (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-generalpgsql-hackers
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(at)sss(dot)pgh(dot)pa(dot)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/

In response to

Responses

pgsql-hackers by date

Next:From: Greg StarkDate: 2009-03-31 14:43:37
Subject: Re: string_to_array with empty input
Previous:From: Tom LaneDate: 2009-03-31 14:35:54
Subject: Re: [GENERAL] pgstattuple triggered checkpoint failure and database outage?

pgsql-general by date

Next:From: Greg StarkDate: 2009-03-31 14:43:37
Subject: Re: string_to_array with empty input
Previous:From: Tom LaneDate: 2009-03-31 14:35:54
Subject: Re: [GENERAL] pgstattuple triggered checkpoint failure and database outage?

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group