subject:"\[HACKERS\] \[GENERAL\] string_to

On Thu, Apr 2, 2009 at 2:50 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane  wrote:
>>> If there's a camp that actually *wants* a NULL result for this case,
>>> I missed the reasoning.
>
>> So that we don't break existing apps because of an issue that is
>> trivial to work around.
>
> We would only be breaking them if a NULL result were actually the
> correct behavior for the application's requirements, which seems
> a bit unlikely.

But that's completely untrue.  If the most useful behavior is either
ARRAY[''] or ARRAY[], then there are presumably lots and lots of
people out there who have apps that do COALESCE(string_to_array(...),
something).  Whichever way you change string_to_array() will break all
of the people doing this who wanted the opposite behavior for no good
reason.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread David E. Wheeler


On Apr 2, 2009, at 11:24 AM, Sam Mason wrote:

Yes, I'd be tempted to pick one and go with it.  It's seems a  
completely

arbitrary choice one way or the other but the current behaviour is
certainly wrong.

I'd go with returning a zero element array because it would do
the "right thing" more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
"array_to_string" and it seems to do the "right" thing for the  
majority

of the cases.


Forgive me if I'm missing something, but it seems to me that  
array_to_string() works either way, no?


try=# select '"' || array_to_string('{}'::text[], ',') || '"'; ?column?
--
 ""
(1 row)

Time: 72.129 ms
try=# select '"' || array_to_string('{""}'::text[], ',') || '"';
 ?column?
--
 ""
(1 row)

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread Tom Lane

Robert Haas  writes:
> On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane  wrote:
>> If there's a camp that actually *wants* a NULL result for this case,
>> I missed the reasoning.

> So that we don't break existing apps because of an issue that is
> trivial to work around.

We would only be breaking them if a NULL result were actually the
correct behavior for the application's requirements, which seems
a bit unlikely.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Thu, Apr 2, 2009 at 2:18 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane  wrote:
>>> Right at the moment, if we stick with the historical definition
>>> of the function, *both* camps have to write out their choice of
>>> the above.  Seems like this is the worst of all possible worlds.
>>> We should probably pick one or the other.
>
>> ISTM there are three camps.
>
> If there's a camp that actually *wants* a NULL result for this case,
> I missed the reasoning.  AFAICS we can either say that every application
> is going to have to put in a CASE wrapper around this function, or say
> that we'll make it do the right thing for some of them and the rest have
> to put the same wrapper around it.

So that we don't break existing apps because of an issue that is
trivial to work around.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread Sam Mason

On Thu, Apr 02, 2009 at 02:04:41PM -0400, Tom Lane wrote:
> A correct fix
> outside-the-function would look more like
> 
> case when str = '' then '{}'::text[] else string_to_array(str, ',') end
> 
> which should correctly yield NULL for NULL input and an empty array
> for empty input.  Similarly, if someone wanted to force the
> single-empty-string result, they should do
> 
> case when str = '' then '{""}'::text[] else string_to_array(str, ',') end
> 
> which also still yields NULL if str is NULL.
> 
> Right at the moment, if we stick with the historical definition
> of the function, *both* camps have to write out their choice of
> the above.  Seems like this is the worst of all possible worlds.
> We should probably pick one or the other.

Yes, I'd be tempted to pick one and go with it.  It's seems a completely
arbitrary choice one way or the other but the current behaviour is
certainly wrong.

I'd go with returning a zero element array because it would do
the "right thing" more often when paired with array_to_string.
I've also been through the first few pages of a Google search for
"array_to_string" and it seems to do the "right" thing for the majority
of the cases.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread Tom Lane

Robert Haas  writes:
> On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane  wrote:
>> Right at the moment, if we stick with the historical definition
>> of the function, *both* camps have to write out their choice of
>> the above.  Seems like this is the worst of all possible worlds.
>> We should probably pick one or the other.

> ISTM there are three camps.

If there's a camp that actually *wants* a NULL result for this case,
I missed the reasoning.  AFAICS we can either say that every application
is going to have to put in a CASE wrapper around this function, or say
that we'll make it do the right thing for some of them and the rest have
to put the same wrapper around it.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Thu, Apr 2, 2009 at 2:04 PM, Tom Lane  wrote:
> Right at the moment, if we stick with the historical definition
> of the function, *both* camps have to write out their choice of
> the above.  Seems like this is the worst of all possible worlds.
> We should probably pick one or the other.

ISTM there are three camps.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread Tom Lane

"David E. Wheeler"  writes:
>> Or we could stick to the current behavior and say "use COALESCE() to
>> resolve the ambiguity, if you need to".

> Steve has a point that leaving it as-is leaves it as impossible to  
> tell the difference between string_to_array(NULL, ',') and  
> string_to_array('', ','). The former properly handles an unknown  
> value, while the latter, where '' is a known value, seems weird to be  
> returning NULL.

Yeah, COALESCE is an abuse of a convenient notation, which will fall
over if you also want NULL to yield NULL.  A correct fix
outside-the-function would look more like

case when str = '' then '{}'::text[] else string_to_array(str, ',') end

which should correctly yield NULL for NULL input and an empty array
for empty input.  Similarly, if someone wanted to force the
single-empty-string result, they should do

case when str = '' then '{""}'::text[] else string_to_array(str, ',') end

which also still yields NULL if str is NULL.

Right at the moment, if we stick with the historical definition
of the function, *both* camps have to write out their choice of
the above.  Seems like this is the worst of all possible worlds.
We should probably pick one or the other.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Thu, Apr 2, 2009 at 12:10 PM, David E. Wheeler  wrote:
> On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:
>
>>> 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.
>
> It's easy enough to write a function to do it:
>
> CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
>    SELECT ARRAY(
>        SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE $1[i] END
>          FROM generate_series(1, array_upper($1, 1)) s(i)
>         ORDER BY i
>    );
> $$ LANGUAGE SQL IMMUTABLE;

Ah!  Thanks for the tip.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Thu, Apr 2, 2009 at 12:17 PM, David E. Wheeler  wrote:
> On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:
>
>> Another way to state the point is that we can offer people a choice of
>> two limitations: string_to_array doesn't work for zero-length lists,
>> or string_to_array doesn't work for empty strings (except most of the
>> time, it does).  The former is sounding less likely to bite people
>> unexpectedly.
>
> Right, very well put.
>
>> Or we could stick to the current behavior and say "use COALESCE() to
>> resolve the ambiguity, if you need to".
>
> Steve has a point that leaving it as-is leaves it as impossible to tell the
> difference between string_to_array(NULL, ',') and string_to_array('', ',').
> The former properly handles an unknown value, while the latter, where '' is
> a known value, seems weird to be returning NULL.

*shrug* CASE WHEN blah IS NOT NULL THEN string_to_array(blah, ',') END

More and more I'm leaning toward leaving this alone.  No matter how
you define it, the behavior can be changed to whichever alternative
you prefer with a 1-line case statement.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread David E. Wheeler


On Apr 1, 2009, at 2:22 PM, Tom Lane wrote:


Another way to state the point is that we can offer people a choice of
two limitations: string_to_array doesn't work for zero-length lists,
or string_to_array doesn't work for empty strings (except most of the
time, it does).  The former is sounding less likely to bite people
unexpectedly.


Right, very well put.


Or we could stick to the current behavior and say "use COALESCE() to
resolve the ambiguity, if you need to".


Steve has a point that leaving it as-is leaves it as impossible to  
tell the difference between string_to_array(NULL, ',') and  
string_to_array('', ','). The former properly handles an unknown  
value, while the latter, where '' is a known value, seems weird to be  
returning NULL.


Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread David E. Wheeler


On Apr 1, 2009, at 12:19 PM, Robert Haas wrote:


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.


It's easy enough to write a function to do it:

CREATE OR REPLACE FUNCTION trim_blanks (anyarray) RETURNS anyarray AS $$
SELECT ARRAY(
SELECT CASE WHEN $1[i] IS NULL OR $1[i] = '' THEN '0' ELSE  
$1[i] END

  FROM generate_series(1, array_upper($1, 1)) s(i)
 ORDER BY i
);
$$ LANGUAGE SQL IMMUTABLE;

Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

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

Ah, sorry, I mis-stated it slightly.  I was imagining that col2 and
col1 where arrays, and f was a function between the base types, not
the array types.

> 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 :)

Probably.  :-)

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-02 Thread Tino Wildenhain


Robert Haas wrote:

On Wed, Apr 1, 2009 at 3:49 PM, Sam Mason  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  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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Steve Crawford


Tom Lane wrote:


I'm starting to vacillate again.  It's clear that for the purposes
of string_to_array, an empty input string is fundamentally ambiguous:
it could mean a list of no things, or a list of one empty thing.
  
Agreed. Of the two, a list of one empty thing makes string_to_array 
closer to an inverse of array_to_string.



Or we could stick to the current behavior and say "use COALESCE() to
resolve the ambiguity, if you need to".

  
Currently string_to_array(null, ',') yields a null result - 
indistinguishable from string_to_array('',','). Wrapping in coalesce 
does not help distinguish true null input from empty-string input. I'm 
not sure at the moment what other cases exist where non-null input 
generates null output.


If the decision is to leave the behavior unchanged, it at least cries 
out for a documentation patch.


Cheers,
Steve


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 1, 2009 at 5:22 PM, Tom Lane  wrote:
> Or we could stick to the current behavior and say "use COALESCE() to
> resolve the ambiguity, if you need to".

If there's no consensus on changing the behavior, it's probably better
to be backward compatible than not.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 1, 2009 at 3:49 PM, Sam Mason  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  
>> 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Tom Lane

Robert Haas  writes:
> If you take 0 items of any type whatsoever and join them together with
> commas, you will get the empty string.  It is also true that if you
> join 1 item together with commas, you will get that item back, and if
> that item is the empty string, you will now have the empty string.  I
> think it's better to worry more about the first case because it
> applies to any type at all, whereas the latter case ONLY applies in
> situations where the empty string is a potentially legal value.

I'm starting to vacillate again.  It's clear that for the purposes
of string_to_array, an empty input string is fundamentally ambiguous:
it could mean a list of no things, or a list of one empty thing.
So the two cases in which an application can safely make use of this
function are
(1) if lists of no things never happen.
(2) if lists never contain empty things.
Either rule allows us to resolve the ambiguity.  We've been discussing
the fact that (2) is an okay assumption for many non-text data types,
but none-the-less string_to_array is in itself a text function and (2)
is not very good for text.  Making this worse, the format *appears*
to work fine for empty strings, so long as you don't have exactly
one of them.  So it seems like applications might be much more likely
to violate (2) than (1).

Another way to state the point is that we can offer people a choice of
two limitations: string_to_array doesn't work for zero-length lists,
or string_to_array doesn't work for empty strings (except most of the
time, it does).  The former is sounding less likely to bite people
unexpectedly.

Or we could stick to the current behavior and say "use COALESCE() to
resolve the ambiguity, if you need to".

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Justin





Martin Gainty wrote:

  
  Split strings into array elements using provided
delimiter
  
string_to_array('xx~^~yy~^~zz', '~^~') 
output: {xx,yy,zz}
  
http://www.postgresql.org/docs/8.3/interactive/functions-array.html
  

Sorry thats not the question i'm asking.  

We are debating if it makes sense to change the output in certain
cases.  

I'm for not returning nulls or returning zero element array.  

I'm asking how is the other better by giving a real world example???  I
don't see the plus side at the moment.

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Martin Gainty


Split strings into array elements using provided delimiter



string_to_array('xx~^~yy~^~zz', '~^~') 

output: {xx,yy,zz}


http://www.postgresql.org/docs/8.3/interactive/functions-array.html

?
Martin 
__ 
Disclaimer and confidentiality note 
This message is confidential and may be privileged. If you are not the intended 
recipient, we kindly ask you to  please inform the sender. Any unauthorised 
dissemination or copying hereof is prohibited. This message serves for 
information purposes only and shall not have any legally binding effect. Given 
that e-mails can easily be subject to manipulation, we can not accept any 
liability for the content provided.






> Date: Wed, 1 Apr 2009 15:49:42 -0400
> From: jus...@emproshunts.com
> To: robertmh...@gmail.com
> CC: t...@sss.pgh.pa.us; st...@enterprisedb.com; s...@samason.me.uk; 
> pgsql-gene...@postgresql.org; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] [GENERAL] string_to_array with empty input
> 
> If someone can show me a real world example  this logic simplifies the 
> code and has more uses I'll bite
> 
> 
> I just presently can't see how this works better.
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_
Rediscover Hotmail®: Get e-mail storage that grows with you. 
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread justin

If someone can show me a real world example  this logic simplifies the 
code and has more uses I'll bite



I just presently can't see how this works better.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [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  
> 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 1, 2009 at 1:05 PM, justin  wrote:
> I'm still a hold out,  We are taking a string putting it into a array based
> on a delimiter.  That is very simple and straight forward.  Yet many argue
> if we want to cast this into another data type the function should deal with
> in limited cases.
>
> string_to_array('',',')::INT[]  works as proposed
>
> But
> string_to_array(',,,', ',' )::INT[]   Fails
> or
> string_to_array('1,2,,4', ',' )::INT[] Fails .

But... but... those aren't comma-separated lists of integers.  If they
were, it would work.

string_to_array('cow,dog,horse')::INT[] will also fail.

If you take 0 items of any type whatsoever and join them together with
commas, you will get the empty string.  It is also true that if you
join 1 item together with commas, you will get that item back, and if
that item is the empty string, you will now have the empty string.  I
think it's better to worry more about the first case because it
applies to any type at all, whereas the latter case ONLY applies in
situations where the empty string is a potentially legal value.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 1, 2009 at 12:52 PM, David E. Wheeler  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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 01, 2009 at 07:40:16PM +0100, Greg Stark wrote:
> The existing behaviour of returning NULL is the only "consistent"
> choice since the correct value is "unknown". And one could argue that
> it's easier to replace NULL with the correct value if the programmer
> knows using coalesce than it is to replace either "" or {""}.

Couldn't a similar argument be applied for division by zero?  Since it's
not known whether the user wants to get a "divide by zero" exception or
"infinity" PG should return NULL and punt the choice to the user.

I think everybody would agree that this would be a bad thing to do!

> But I'm
> still leaning to thinking that using an arbitrary choice that at least
> gets most users intentions is better.

I'd agree; returning NULL and not forcing the user to make a choice
is a bad design decision---the user doesn't need to put a coalesce
in and hence their code will probably break in strange ways when
they're not expecting it.  Nobody suggest adding a third parameter to
string_to_array, please!

The general mantra that seems to apply here is "one good option is
better than two bad ones".

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Greg Stark

On Wed, Apr 1, 2009 at 6:23 PM, David E. Wheeler  wrote:
> Right, it's making a special case of '', which does seem rather inconsistent
> to me.

"David E. Wheeler"  writes:

> On Apr 1, 2009, at 10:05 AM, justin wrote:
>
>> string_to_array('',',')::INT[]  works as proposed
>>
>> But
>> string_to_array(',,,', ',' )::INT[]   Fails
>> or
>> string_to_array('1,2,,4', ',' )::INT[] Fails .
>>
>>
>> I'm trying to understand the difference between a empty string to a  string
>> with  many blank entries between  the delimiter.

Well, uh, in one case it's empty and in the other case it's not?

>> Consider   ',,'  = ''  once the delimiter is removed .  Yet  Seven zero
>> length entries were passed.  How is that going to be  handled

Well it's pretty clear empty delimiters cannot be handled consistently. Some
languages handle them as a special case (splitting every character into a
separate string, for example -- which I'll point out will result in an empty
array as a result for an empty string input) or make it an error.

> Right, it's making a special case of '', which does seem rather  inconsistent
> to me.

It's not a special case -- or it's a special case whichever we choose,
depending on which way you look at it.

What we're talking about here is replacing the blank values in the following
tables. We can get either the first one right in both cases with {} as the
result, or we can get the second one right in the second table with {""}.
Either way there is an inconsistency in at least one case.

The existing behaviour of returning NULL is the only "consistent" choice since
the correct value is "unknown". And one could argue that it's easier to
replace NULL with the correct value if the programmer knows using coalesce
than it is to replace either "" or {""}. But I'm still leaning to thinking
that using an arbitrary choice that at least gets most users intentions is
better.

postgres=# select input,
string_to_array(array_to_string(input,','),',') as output from (values
(array[]::text[]),(array['foo']),(array['foo','bar']),(array['foo','bar','baz']))
as input(input);
 input |output
---+---
 {}|
 {foo} | {foo}
 {foo,bar} | {foo,bar}
 {foo,bar,baz} | {foo,bar,baz}
(4 rows)

postgres=# select input,
string_to_array(array_to_string(input,','),',') as output from (values
(array[]::text[]),(array['']),(array['','']),(array['','',''])) as
input(input);
   input|   output
+
 {} |
 {""}   |
 {"",""}| {"",""}
 {"","",""} | {"","",""}
(4 rows)

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Wed, Apr 01, 2009 at 10:23:18AM -0700, David E. Wheeler wrote:
> On Apr 1, 2009, at 10:05 AM, justin wrote:
> >string_to_array('',',')::INT[]  works as proposed
> >
> >But
> >string_to_array(',,,', ',' )::INT[]   Fails
> >or
> >string_to_array('1,2,,4', ',' )::INT[] Fails .
> >
> >
> >I'm trying to understand the difference between a empty string to a  
> >string with  many blank entries between  the delimiter.
> >Consider   ',,'  = ''  once the delimiter is removed .  Yet  
> >Seven zero length entries were passed.  How is that going to be  
> >handled
> 
> Right, it's making a special case of '', which does seem rather  
> inconsistent to me.

Yes it is; but it's a useful special case because it allows:

  string_to_array(array_to_string(col,','),',')

to do the right thing whether it's got zero or more elements in.  With
the current implementation you get a NULL back in the case of zero
elements and the expected array back the rest of the time.

To me, it doesn't really matter whether:

  string_to_array(',', ',' )::INT[]

fails or not; because array_to_string will never generate a string that
looks like this.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread David E. Wheeler


On Apr 1, 2009, at 10:05 AM, justin wrote:


string_to_array('',',')::INT[]  works as proposed

But
string_to_array(',,,', ',' )::INT[]   Fails
or
string_to_array('1,2,,4', ',' )::INT[] Fails .


I'm trying to understand the difference between a empty string to a  
string with  many blank entries between  the delimiter.
Consider   ',,'  = ''  once the delimiter is removed .  Yet  
Seven zero length entries were passed.  How is that going to be  
handled


Right, it's making a special case of '', which does seem rather  
inconsistent to me.


Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread David E. Wheeler


On Apr 1, 2009, at 10:09 AM, Tom Lane wrote:


Thus, this is not a problem with string_to_array(), but a
casting problem from text[] to int[].


Nonsense.  The question is whether string_to_array is meant to be  
useful

for lists of anything except text.  I agree you could argue that it
isn't.  But even in the domain of text it's not all that cut-and-dried
whether string_to_array should return array[] or array[''] for empty
input.  So ISTM we're giving up less than we gain by choosing the  
former.


Yeah. I'm okay with either, as long as it's consistent. I have a mild  
preference for '{""}', but I can live with ARRAY[] instead. As long as  
it's not NULL that gets returned.


Best,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Tom Lane

"David E. Wheeler"  writes:
> Well, I'd just point out that the return value of string_to_array() is  
> text[].

True...

> Thus, this is not a problem with string_to_array(), but a  
> casting problem from text[] to int[].

Nonsense.  The question is whether string_to_array is meant to be useful
for lists of anything except text.  I agree you could argue that it
isn't.  But even in the domain of text it's not all that cut-and-dried
whether string_to_array should return array[] or array[''] for empty
input.  So ISTM we're giving up less than we gain by choosing the former.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread justin





Tom Lane wrote:

  Robert Haas  writes:
  
  
On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark  wrote:


  On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason  wrote:
  
  
string_to_array('',',')::INT[]  => invalid input syntax for integer: ""

  
  Oof. That's a good point.
  

  
  
  
  
+1.  I find this argument much more compelling than anything else
that's been offered up so far.

  
  
Yeah.  It seems to me that if you consider only the case where the array
elements are text, there's a weak preference for considering '' to be a
single empty string; but as soon as you think about any other datatype,
there's a strong preference to consider it a zero-element list.  So I
too have come around to favor the latter interpretation.  Do we have
any remaining holdouts?

			regards, tom lane
  


I'm still a hold out,  We are taking a string putting it into a array
based on a delimiter.  That is very simple and straight forward.  Yet
many argue if we want to cast this into another data type the function
should deal with in limited cases.  

string_to_array('',',')::INT[]  works as proposed 

But 
string_to_array(',,,', ',' )::INT[]   Fails 
or
string_to_array('1,2,,4', ',' )::INT[] Fails .


I'm trying to understand the difference between a empty string to a
string with  many blank entries between  the delimiter.    
Consider  
',,'  = ''  once the delimiter is removed .  Yet Seven zero length
entries were passed.  How is that going to
be handled  

In one case it works and yet other cases it fails.  This is
inconsistent behavior.  Unless all zero length strings are removed or
are treated as NULLs  I can't see how casting to another type is going
to work.

If zero length strings are treated as NULLs this creates idea that zero
length strings are = to NULLs. 

The input is a string and the output is text[],  casting to another
data type is error prone and should be handled by the programmer.

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread David E. Wheeler


On Apr 1, 2009, at 9:02 AM, Tom Lane wrote:


+1.  I find this argument much more compelling than anything else
that's been offered up so far.


Yeah.  It seems to me that if you consider only the case where the  
array
elements are text, there's a weak preference for considering '' to  
be a
single empty string; but as soon as you think about any other  
datatype,

there's a strong preference to consider it a zero-element list.  So I
too have come around to favor the latter interpretation.  Do we have
any remaining holdouts?


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;

So I split the string into an array, and then remove unreasonable  
values. This also allows me to set defaults:


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


So I still think that string_to_array('', ',') should return '{""}',  
and how casting is handled should be left to the user to flexibly  
handle.


That said, I'm not seeing a simple function for modifying an array.  
I'd have to write one for each specific case. :-(


Best,

David




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-04-01 Thread Tom Lane

Robert Haas  writes:
> On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark  wrote:
>> On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason  wrote:
>>> string_to_array('',',')::INT[]  => invalid input syntax for integer: ""
>> 
>> Oof. That's a good point.

> +1.  I find this argument much more compelling than anything else
> that's been offered up so far.

Yeah.  It seems to me that if you consider only the case where the array
elements are text, there's a weak preference for considering '' to be a
single empty string; but as soon as you think about any other datatype,
there's a strong preference to consider it a zero-element list.  So I
too have come around to favor the latter interpretation.  Do we have
any remaining holdouts?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Tue, Mar 31, 2009 at 10:44 AM, Greg Stark  wrote:
> On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason  wrote:
>>
>>  string_to_array('',',')::INT[]  => invalid input syntax for integer: ""
>
> Oof. That's a good point.

+1.  I find this argument much more compelling than anything else
that's been offered up so far.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

 [ oops, forgot to send this to -hackers before ]

On Tue, Mar 31, 2009 at 05:08:45PM +0100, Greg Stark wrote:
> Both interpretations are clearly consistent but it depends on whether
> you think it's a bunch of text strings concatenated together or if
> it's a list of objects.
> 
> The example of string_to_array('',',')::int[] is relevant to this
> point. The whole "there's one empty element" only makes sense if
> you're thinking in terms of string processing. If it's a list of any
> other kind of object it probably doesn't make sense; you can't say
> there's one empty integer or one empty composite object or one empty
> anything else.

I think this is about the only sensible option, but my reasoning is
somewhat different.

My original intuition was that array_to_string and string_to_array
should be (perfect) inverses of each other.  Unfortunately I can't see
any way to get this to happen; zero length arrays or NULL elements
in the array mean than array_to_string isn't injective.  This means
that the composition of the two functions won't result in an injective
function and my original premise is false.  Note that as far as I can
tell string_to_array is injective.  I'm assuming that the delimiter
won't appear as part of an element of the array; e.g. an array of
integers and space as a delimiter is OK, but using the same delimiter
with unconstrained text is not OK, a blank delimiter is never OK as it's
always part of a string.

"Injective" means there exists more than one array that encodes to the
same string.  The examples being how do you sanely encode '{}' and
'{NULL}' in a unique way; '{""}' is a bad example because it's just
an artifact of how strings are represented.  The complications needed
to allow this to happen would make it a very similar function as the
array's normal output_function function and hence wouldn't serve a
useful purpose.  All of this implies that we have to make a compromise
somewhere.

The semantics that most closely match the existing behaviour would be;
for array_to_string:

  1) remove NULL values from input array
  2) call output_function on remaining elements
  3) intersperse[1] the delimiter between the remaining elements
  4) concatenate the resulting array

for string_to_array:

  1) check if input is zero length; return empty array
  2) split array based on delimiter and return

Note that both functions are STRICT; i.e. a NULL for either parameter
should cause the function to return NULL.  Arguably in string_to_array
it could be non-strict if the input string is empty, but it's probably
worth keeping it strict to simplify the semantics.

Here are some examples:

  array_to_string('{}'::TEXT[],',')  => ''
  array_to_string('{NULL}'::TEXT[],',')  => ''
  array_to_string('{NULL,NULL}'::TEXT[],',') => ''
  array_to_string('{a,NULL}'::TEXT[],',')=> 'a'
  array_to_string('{NULL,a}'::TEXT[],',')=> 'a'
  array_to_string('{a,b}'::TEXT[],',')   => 'a,b'
  array_to_string('{a,NULL,b}'::TEXT[],',')  => 'a,b'

  string_to_array('',',') => '{}'
  string_to_array(' ',',')=> '{" "}'
  string_to_array(',',',')=> '{"",""}'
  string_to_array('a',',')=> '{a}'
  string_to_array('a,',',')   => '{a,""}'
  string_to_array(',a',',')   => '{"",a}'
  string_to_array('a,b',',')  => '{a,b}'

My thinking before was that it should be doing:

  string_to_array('',',') => '{""}'

instead, but I now think that Greg has a point and these are nicer/more
generally useful semantics.

Hum, that all got a bit more verbose than I was expecting.  Ah well, I
hope it's somewhat useful.

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

 [1] as in the intersperse function in Haskell
   http://www.haskell.org/onlinereport/list.html#sect17.3
   intersperse "#" ["a", "bar"] == ["a", "#", "bar"]
 note that here we're working with arrays of string, rather than
 arrays of characters as in the report.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

This thread being cross posted has made it a bit
confusing

Greg Stark wrote:

Nobody has ever suggested filtering out empty elements or dealing
specially with spaces or anything else like that. If you're talking
about that then you've missed the original question.

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

That means to me dropping empty strings or removing values that don't make sense. Then the argument begins what values make since to drop. Just zero length strings or include strings with million white spaces???

One last try.

If there's a column called "shopping list" which is a comma-separated
list of items to buy in the store and I store '' in it. How many items
do you think that means you should go shopping for? Do you think
that's one item that should be rejected because it's an empty string?
Or do you think that's an empty list with zero items listed?

It can't be rejected, Its an Empty shopping
list although a worthless shopping list .

What would it look like if it was a zero-length list? You can ask what
would it look like if it was a shopping list of one item called ''.
But I agree both are theoretically consistent, but one is actually
useful in 99% of use cases. The other is only useful in unusual cases.

I'm still confused which one you want here

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Tue, Mar 31, 2009 at 6:44 PM, justin  wrote:
>
> Consider this.  I have intelligent part numbers  that need to be split apart
> to simplify searching  and do math with.
>
> string_to_array(' F-2500-50 ', '-' ) ::int[]

Yeah, that's what I've said about three times. If you're building a
parser and think of it in terms of string parsing then yes, a
singletone array makes sense.

> Still fails with an error as expected.  what is the difference between ' '
> and 'F'

I don't know what you're talking about. The question is how many
elements are in ''. No space. And no separators. To repeat for the
last time. If you think in terms of string processing then the answer
1 is reasonable. But if you think it's a list of separate items then
anyone will say that's an empty list and contains no elements.

Nobody has ever suggested filtering out empty elements or dealing
specially with spaces or anything else like that. If you're talking
about that then you've missed the original question.

One last try.

If there's a column called "shopping list" which is a comma-separated
list of items to buy in the store and I store '' in it. How many items
do you think that means you should go shopping for? Do you think
that's one item that should be rejected because it's an empty string?
Or do you think that's an empty list with zero items listed?

What would it look like if it was a zero-length list? You can ask what
would it look like if it was a shopping list of one item called ''.
But I agree both are theoretically consistent, but one is actually
useful in 99% of use cases. The other is only useful in unusual cases.
-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input





Greg Stark wrote:

  On Tue, Mar 31, 2009 at 5:48 PM, justin  wrote:
  
  
But consider  this fails also

select string_to_array('1, , 3', ',' )::int[] => ERROR:  invalid input
syntax for integer: " "

yet this works

select string_to_array('1, 2, 3',',')::int[]

  
  

Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.

The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.

I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.
  


I disagree.   Casting a string to something else can be a very error
prone to begin with. 
Having  string_to_array() to deal with that possibility  is out of its
scope IMHO.

Consider this.  I have intelligent part numbers  that need to be split
apart to simplify searching  and do math with. 

string_to_array(' F-2500-50 ', '-' ) ::int[]

Still fails with an error as expected.  what is the difference between
' '  and 'F'  

So before doing any thing a test needs to be done to verify the
contents, so it can be casted to something else.

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Tue, Mar 31, 2009 at 5:48 PM, justin  wrote:
>
> But consider  this fails also
>
> select string_to_array('1, , 3', ',' )::int[] => ERROR:  invalid input
> syntax for integer: " "
>
> yet this works
>
> select string_to_array('1, 2, 3',',')::int[]

Sure, and the analogous pair string_to_array(' ',',')::int[] and
string_to_array('1 ',',')::int[] behave similarly.

The point is that '' could represent no items or one empty string. We
get to pick which one and in any use case where the string was a list
of objects it's almost certainly intended to be an empty list. And
databases are almost always processing lists of things.

I think the only use case where you want it to be a singleton list of
an empty string is when you're doing string parsing such as building a
lexer or something like that, which is isn't a typical use for sql
code.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input


Sam Mason wrote:


  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


  


But consider  this fails also

select string_to_array('1, , 3', ',' )::int[] => ERROR:  invalid input 
syntax for integer: " "


yet this works

select string_to_array('1, 2, 3',',')::int[]

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input


Sam Mason wrote:

I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

  select count_elements(string_to_array('butter,,milk',','))

I think the semantics you want is what you'd get from:

  array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.
  


I agree the function should not be changing values passed.   
Stripping/Dropping empty strings is changing what was passed into the 
function instead breaking it into a array. 




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-03-31 Thread David E. Wheeler


On Mar 31, 2009, at 8:34 AM, Sam Mason wrote:


What do you really expect to be returned for things like

select count_elements(string_to_array('butter,tea,milk',','))


3 = {butter,tea,milk}


select count_elements(string_to_array('butter,tea',','))


2 = {butter,tea}


select count_elements(string_to_array('butter',','))


1 = {butter}


select count_elements(string_to_array('',','))


1 = ARRAY['']


I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

 select count_elements(string_to_array('butter,,milk',','))


3 = ARRAY['butter', '', 'milk']


I think the semantics you want is what you'd get from:

 array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.


Yeah, if I wanted something like that in Perl, I'd do:

  my @stuff = grep { $_ } split /,/, $string;

In no case would I ever expect a NULL, however, unless I was trying to  
split on NULL.


NULL = string_to_array(NULL, ',');

Best,

David


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: Fwd: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input

2009-03-31 Thread Alvaro Herrera

Greg Stark wrote:
> On Tue, Mar 31, 2009 at 4:17 PM, Alvaro Herrera
>  wrote:
> > Greg Stark wrote:
> >> Could the list admin please unsu*scribe Andreas Zeugswetter from the
> >> lists until he can fix his vacation-responder-gone-nuts?
> >
> > I've set him "nomail" on pgsql-hackers.
> 
> Thanks. Is there a better address to reach list admins? I've failed in
> the past to reach anyone mailing various typical patterns and there's
> nothing I could find on the web site.

I don't think so.  Marc is the owner and site admin (I'm just
pgsql-hackers moderator), but he generally takes a long time to answer
these things.  I guess you could call him by phone ...

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: Fwd: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input

On Tue, Mar 31, 2009 at 4:17 PM, Alvaro Herrera
 wrote:
> Greg Stark wrote:
>> Could the list admin please unsubscribe Andreas Zeugswetter from the
>> lists until he can fix his vacation-responder-gone-nuts?
>
> I've set him "nomail" on pgsql-hackers.

Thanks. Is there a better address to reach list admins? I've failed in
the past to reach anyone mailing various typical patterns and there's
nothing I could find on the web site.

Sigh, in principle I would want the policy to be to ban any domain
which ran broken mail software but there's no way that's sane for a
long-time subscriber.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-03-31 Thread Tom Lane

Greg Stark  writes:
> On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason  wrote:
>> string_to_array('',',')::INT[]  => invalid input syntax for integer: ""

> Oof. That's a good point.

Isn't that an argument in favor of the zero-size-array definition?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

2009-03-31 Thread Sam Mason

On Tue, Mar 31, 2009 at 03:43:37PM +0100, Greg Stark wrote:
> On Tue, Mar 31, 2009 at 7:45 AM, Brendan Jurd  wrote:
> > My first thought was that it should be a zero-element array, because
> > then the string_to_array() behaviour would conform to the notion that
> > it returns an array with 1 element per string fragment bounded by the
> > delimiter.
> >
> > However, I note that if you provide an empty delimiter, or one which
> > doesn't occur anywhere in the source string, you get an array with one
> > element, being the entire source string.
> 
> Yeah, actually the more I think about it the more I think it would be
> strange for most uses to get a singleton array for this case.

Really? I think it's strange not to!

> What do you really expect to be returned for things like
> 
> select count_elements(string_to_array('butter,tea,milk',','))
> select count_elements(string_to_array('butter,tea',','))
> select count_elements(string_to_array('butter',','))
> select count_elements(string_to_array('',','))

I'd expect 3,2,1 and 1.

That's also a disingenuous example; what would you expect back from:

  select count_elements(string_to_array('butter,,milk',','))

I think the semantics you want is what you'd get from:

  array_filter_blanks(string_to_array($1,$2))

where I defined "array_filter_blanks" in my previous post.

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: Fwd: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input

2009-03-31 Thread Alvaro Herrera

Greg Stark wrote:
> Could the list admin please unsubscribe Andreas Zeugswetter from the
> lists until he can fix his vacation-responder-gone-nuts?

I've set him "nomail" on pgsql-hackers.


> -- Forwarded message --
> From: Zeugswetter Andreas OSB sIT 
> Date: Tue, Mar 31, 2009 at 3:44 PM
> Subject: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input
> To: Greg Stark 
> 
> 
> Automatic reply: I will not be reading mail until 1.4.2009. For urgent
> matters please contact my colleagues.
> 
> Greetings
> Andreas Zeugswetter
> 
> 


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fwd: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input

Could the list admin please unsubscribe Andreas Zeugswetter from the
lists until he can fix his vacation-responder-gone-nuts?

-- Forwarded message --
From: Zeugswetter Andreas OSB sIT 
Date: Tue, Mar 31, 2009 at 3:44 PM
Subject: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input
To: Greg Stark 

Automatic reply: I will not be reading mail until 1.4.2009. For urgent
matters please contact my colleagues.

Greetings
Andreas Zeugswetter

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input

On Tue, Mar 31, 2009 at 3:42 PM, Sam Mason  wrote:
>
>  string_to_array('',',')::INT[]  => invalid input syntax for integer: ""

Oof. That's a good point.


-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [GENERAL] string_to_array with empty input