Skip to content

Commit 33f4372

Browse files
tglsfdctglsfdc
committed
Add three-parameter forms of array_to_string and string_to_array, to allow
better handling of NULL elements within the arrays. The third parameter is a string that should be used to represent a NULL element, or should be translated into a NULL element, respectively. If the third parameter is NULL it behaves the same as the two-parameter form. There are two incompatible changes in the behavior of the two-parameter form of string_to_array. First, it will return an empty (zero-element) array rather than NULL when the input string is of zero length. Second, if the field separator is NULL, the function splits the string into individual characters, rather than returning NULL as before. These two changes make this form fully compatible with the behavior of the new three-parameter form. Pavel Stehule, reviewed by Brendan Jurd
1 parent 5148a04 commit 33f4372

File tree

9 files changed

+381
-86
lines changed

9 files changed

+381
-86
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 39 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.525 2010/08/08 19:15:27 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.526 2010/08/10 21:51:00 tgl Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -9736,13 +9736,14 @@ SELECT NULLIF(value, '(none)') ...
97369736
<row>
97379737
<entry>
97389738
<literal>
9739-
<function>array_to_string</function>(<type>anyarray</type>, <type>text</type>)
9739+
<function>array_to_string</function>(<type>anyarray</type>, <type>text</type> <optional>, <type>text</type></optional>)
97409740
</literal>
97419741
</entry>
97429742
<entry><type>text</type></entry>
9743-
<entry>concatenates array elements using supplied delimiter</entry>
9744-
<entry><literal>array_to_string(ARRAY[1, 2, 3], '~^~')</literal></entry>
9745-
<entry><literal>1~^~2~^~3</literal></entry>
9743+
<entry>concatenates array elements using supplied delimiter and
9744+
optional null string</entry>
9745+
<entry><literal>array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')</literal></entry>
9746+
<entry><literal>1,2,3,*,5</literal></entry>
97469747
</row>
97479748
<row>
97489749
<entry>
@@ -9758,13 +9759,14 @@ SELECT NULLIF(value, '(none)') ...
97589759
<row>
97599760
<entry>
97609761
<literal>
9761-
<function>string_to_array</function>(<type>text</type>, <type>text</type>)
9762+
<function>string_to_array</function>(<type>text</type>, <type>text</type> <optional>, <type>text</type></optional>)
97629763
</literal>
97639764
</entry>
97649765
<entry><type>text[]</type></entry>
9765-
<entry>splits string into array elements using supplied delimiter</entry>
9766-
<entry><literal>string_to_array('xx~^~yy~^~zz', '~^~')</literal></entry>
9767-
<entry><literal>{xx,yy,zz}</literal></entry>
9766+
<entry>splits string into array elements using supplied delimiter and
9767+
optional null string</entry>
9768+
<entry><literal>string_to_array('xx~^~yy~^~zz', '~^~', 'yy')</literal></entry>
9769+
<entry><literal>{xx,NULL,zz}</literal></entry>
97689770
</row>
97699771
<row>
97709772
<entry>
@@ -9781,6 +9783,34 @@ SELECT NULLIF(value, '(none)') ...
97819783
</tgroup>
97829784
</table>
97839785

9786+
<para>
9787+
In <function>string_to_array</function>, if the delimiter parameter is
9788+
NULL, each character in the input string will become a separate element in
9789+
the resulting array. If the delimiter is an empty string, then the entire
9790+
input string is returned as a one-element array. Otherwise the input
9791+
string is split at each occurrence of the delimiter string.
9792+
</para>
9793+
9794+
<para>
9795+
In <function>string_to_array</function>, if the null-string parameter
9796+
is omitted or NULL, none of the substrings of the input will be replaced
9797+
by NULL.
9798+
In <function>array_to_string</function>, if the null-string parameter
9799+
is omitted or NULL, any null elements in the array are simply skipped
9800+
and not represented in the output string.
9801+
</para>
9802+
9803+
<note>
9804+
<para>
9805+
There are two differences in the behavior of <function>string_to_array</>
9806+
from pre-9.1 versions of <productname>PostgreSQL</>.
9807+
First, it will return an empty (zero-element) array rather than NULL when
9808+
the input string is of zero length. Second, if the delimiter string is
9809+
NULL, the function splits the input into individual characters, rather
9810+
than returning NULL as before.
9811+
</para>
9812+
</note>
9813+
97849814
<para>
97859815
See also <xref linkend="functions-aggregate"> about the aggregate
97869816
function <function>array_agg</function> for use with arrays.

‎src/backend/utils/adt/array_userfuncs.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Copyright (c) 2003-2010, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.35 2010/02/26 02:01:06 momjian Exp $
9+
* $PostgreSQL: pgsql/src/backend/utils/adt/array_userfuncs.c,v 1.36 2010/08/10 21:51:00 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -407,9 +407,11 @@ ArrayType *
407407
create_singleton_array(FunctionCallInfo fcinfo,
408408
Oid element_type,
409409
Datum element,
410+
bool isNull,
410411
int ndims)
411412
{
412413
Datum dvalues[1];
414+
bool nulls[1];
413415
int16 typlen;
414416
bool typbyval;
415417
char typalign;
@@ -429,6 +431,7 @@ create_singleton_array(FunctionCallInfo fcinfo,
429431
ndims, MAXDIM)));
430432

431433
dvalues[0] = element;
434+
nulls[0] = isNull;
432435

433436
for (i = 0; i < ndims; i++)
434437
{
@@ -462,7 +465,7 @@ create_singleton_array(FunctionCallInfo fcinfo,
462465
typbyval = my_extra->typbyval;
463466
typalign = my_extra->typalign;
464467

465-
return construct_md_array(dvalues, NULL, ndims, dims, lbs, element_type,
468+
return construct_md_array(dvalues, nulls, ndims, dims, lbs, element_type,
466469
typlen, typbyval, typalign);
467470
}
468471

0 commit comments

Comments
 (0)