Re: [GENERAL] string_to_array with empty input

On Wed, Apr 1, 2009 at 3:49 PM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> 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;

map { closure } @list applies closure to each element of list and
makes a new list out of the results.
grep { closure } @list applies closure to each element of list and
returns the list elements for which the closure returns true.

>> 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.

I'm speaking primarily of functions as first-class objects, though
closures would be nice too. But consider an operation like

UPDATE rel SET col1 = MAP ( f OVER col2 )

We need to be able to determine whether this is well-typed, just as we
do now for any other SQL query. Specifically, we need to check that f
is a one argument function whose argument type is that of col2 and
whose return type is that of col1. My understanding is that right now
types are represented as 32-bit OIDs. I think they'd need to be some
sort of more complex structure in order to handle cases like this.

...Robert