pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedarray_to_string
6+ messages / 3 participants
[nested] [flat]
* array_to_string
@ 2007-03-27 06:47 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 6+ messages in thread
From: Sabin Coanda @ 2007-03-27 06:47 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I used the function array_to_string, and I found it ignores NULL values,
e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
The function documentation doesn't explain this case.
So please tell me: is it the normal behavior or a bug ?
TIA,
Sabin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: array_to_string
@ 2007-03-27 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2007-03-27 14:49 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-sql
"Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
> I used the function array_to_string, and I found it ignores NULL values,
> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
Do you have a better idea?
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: array_to_string
@ 2007-03-27 14:59 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2007-03-27 14:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>; pgsql-sql
Tom Lane wrote:
> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>> I used the function array_to_string, and I found it ignores NULL values,
>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>
> Do you have a better idea?
If you're being strict it should presumably return NULL for the whole
string. Or you'd need a "coalesce-to-this" parameter.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: array_to_string
@ 2007-03-27 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-03-27 15:35 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>; pgsql-sql
Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
>> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>>> I used the function array_to_string, and I found it ignores NULL values,
>>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>>
>> Do you have a better idea?
> If you're being strict it should presumably return NULL for the whole
> string. Or you'd need a "coalesce-to-this" parameter.
If the whole array is NULL it would return NULL, but that seems
unhelpful for a single element being NULL ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: array_to_string
@ 2007-03-27 15:45 Richard Huxton <dev@archonet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2007-03-27 15:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>; pgsql-sql
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Tom Lane wrote:
>>> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>>>> I used the function array_to_string, and I found it ignores NULL values,
>>>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>>> Do you have a better idea?
>
>> If you're being strict it should presumably return NULL for the whole
>> string. Or you'd need a "coalesce-to-this" parameter.
>
> If the whole array is NULL it would return NULL, but that seems
> unhelpful for a single element being NULL ...
It's the least surprising choice though. You'd expect array_to_string()
to be a loop doing something like:
output = output || connector || arr[i]
Of course this will produce NULL if any of the inputs are NULL. Now that
might be unhelpful, but that's NULL for you.
All you're doing is (presumably) adding a hidden test to the loop where
Sabin seemed to expect COALESCE(arr[i],<something>). I'm not sure either
is better across the board.
The problem comes with what <something> should be. You're either looking
at a third parameter or perhaps more cleanly you need a
coalesce_array(ARRAY,VAL) function which produces an output array where
all the null elemts are replaced by VAL.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: array_to_string
@ 2007-06-18 07:17 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Sabin Coanda @ 2007-06-18 07:17 UTC (permalink / raw)
To: pgsql-sql
"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:20090.1175006990@sss.pgh.pa.us...
> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>> I used the function array_to_string, and I found it ignores NULL values,
>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>
> Do you have a better idea?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
I found the function would be useful to build dynamic statements that
includes array items.
For instance I have a function with an array argument of integer. In that
function I'd like to send that argument by a dynamic string to other
function. If array_to_string would return the whole elements, including
NULLs, I'd be able to build that statement. But with the present behavior,
the result statement damages the array structure.
On the other hand, I saw there is available its complement function
string_to_array. It would be nice to be able to reverse a string built by
array_to_string, with string_to_array, getting the original array. This
could happen if the function would consider the NULLs as well.
A simple 'NULL' string would be enough to fulfil this reversing process, so
that array_to_string( ARRAY[1,NULL,3], ',' ) would returns '1,NULL,3'.
A problem will occur when it would be applied to string arrays, because NULL
string, and 'NULL' string value would have the same result. This could be
solved if the string arrays would be formed with the same rules as for SQL
constant syntax. I mean with quotes. So that, array_to_string( ARRAY[ 'a',
'NULL', NULL, 'b'], '/' ) would return not a/NULL/NULL/b , but
'a'/'NULL'/NULL/'b'. Consequently, string_to_array function should interpret
this result.
Regards,
Sabin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-18 07:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-27 06:47 array_to_string Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-03-27 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-27 14:59 ` Richard Huxton <dev@archonet.com>
2007-03-27 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-27 15:45 ` Richard Huxton <dev@archonet.com>
2007-06-18 07:17 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox