Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit33f4372

Browse files
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 parameteris a string that should be used to represent a NULL element, or shouldbe translated into a NULL element, respectively. If the third parameteris NULL it behaves the same as the two-parameter form.There are two incompatible changes in the behavior of the two-parameter formof string_to_array. First, it will return an empty (zero-element) arrayrather than NULL when the input string is of zero length. Second, if thefield separator is NULL, the function splits the string into individualcharacters, rather than returning NULL as before. These two changes makethis form fully compatible with the behavior of the new three-parameter form.Pavel Stehule, reviewed by Brendan Jurd
1 parent5148a04 commit33f4372

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(FunctionCallInfofcinfo,
408408
Oidelement_type,
409409
Datumelement,
410+
boolisNull,
410411
intndims)
411412
{
412413
Datumdvalues[1];
414+
boolnulls[1];
413415
int16typlen;
414416
booltypbyval;
415417
chartypalign;
@@ -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-
returnconstruct_md_array(dvalues,NULL,ndims,dims,lbs,element_type,
468+
returnconstruct_md_array(dvalues,nulls,ndims,dims,lbs,element_type,
466469
typlen,typbyval,typalign);
467470
}
468471

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp