Re: [GENERAL] string_to_array with empty input

On Wed, Apr 01, 2009 at 03:19:23PM -0400, Robert Haas wrote:
> On Wed, Apr 1, 2009 at 12:52 PM, David E. Wheeler <david(at)kineticode(dot)com> wrote:
> > Well, I'd just point out that the return value of string_to_array() is
> > text[]. Thus, this is not a problem with string_to_array(), but a casting
> > problem from text[] to int[]. Making string_to_array() return a NULL for
> > this case to make casting simpler is addressing the problem in the wrong
> > place, IMHO. If I want to do this in Perl, for example, I'd do something
> > like this:
> >
> > my @ints = grep { defined $_ && $_ ne '' } split ',', $string;
>
> I've written code that looks a whole lot like this myself, but there's
> no easy way to do that in SQL. SQL, in particular, lacks closures, so
> grep {} and map {} don't exist. I really, really wish they did, but

I don't grok Perl so I'd appreciate an explanation of what the above
does, at a guess it looks a lot like the function I wrote up thread[1]
called array_filter_blanks and using it would look like:

SELECT array_filter_blanks(string_to_array(arr,',')) AS ints;

> I
> believe that our type system is too woefully pathetic to be up to the
> job.

This has very little to do with PG's type system. You either want
functions to be first class objects or support for closures, blaming the
type system is not correct.

--
Sam http://samason.me.uk/

[1] http://archives.postgresql.org/pgsql-hackers/2009-03/msg01373.php