Re: [GENERAL] string_to_array with empty input - Mailing list pgsql-hackers

Robert Haas wrote:
> On Wed, Apr 1, 2009 at 3:49 PM, Sam Mason <sam@samason.me.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@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 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.

Ah, so thats equal to
 [map_closure(i) for i in thelist if grep_closure(i)]

in python.

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

Would above query not be written as

UPDATE rel SET col1 = f(col2);

anyway or am I missing something?

imho, having generic tuple tables as we have in INSERT INTO (...)
VALUES (...),(...),(...)

to be useable in all places like a real table would be helpful in
many cases.

But this might be completely unrelated :)

Regards
Tino