On Wed, Apr 1, 2009 at 12:52 PM, David E. Wheeler <david@kineticode.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
believe that our type system is too woefully pathetic to be up to the
job. So it seems to me that arguing that SQL (which lacks those
primitives) should match Perl (which has them) isn't really getting us
anywhere.
> my @ints = map { $_ || 0 } split ',', $string;
>
> This ensures that I get the proper number of records in the example of something like '1,2,,4'.
I can't see that there's any way to do this in SQL regardless of how
we define this operation.
...Robert