Hello
I am sending a actualised patch. There is only one significant change
to last patch. Function to_string was renamed to "implode" and
to_array was renamed "explode".
Regards
Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2010-07-23 21:18:04.698690857 +0200
--- ./doc/src/sgml/func.sgml 2010-07-23 21:51:08.860689007 +0200
***************
*** 4652,4658 ****
</para>
<para>
! If case-independent matching is specified,
the effect is much as if all case distinctions had vanished from the
alphabet.
When an alphabetic that exists in multiple cases appears as an
--- 4652,4658 ----
</para>
<para>
! If case-independent matching is specified,
the effect is much as if all case distinctions had vanished from the
alphabet.
When an alphabetic that exists in multiple cases appears as an
***************
*** 9541,9546 ****
--- 9541,9552 ----
<primary>array_upper</primary>
</indexterm>
<indexterm>
+ <primary>explode</primary>
+ </indexterm>
+ <indexterm>
+ <primary>implode</primary>
+ </indexterm>
+ <indexterm>
<primary>string_to_array</primary>
</indexterm>
<indexterm>
***************
*** 9675,9680 ****
--- 9681,9708 ----
<row>
<entry>
<literal>
+ <function>explode</function>(<type>text</type>, <type>text</type> <optional>, <type>text</type></optional>)
+ </literal>
+ </entry>
+ <entry><type>text[]</type></entry>
+ <entry>splits string into array elements using supplied delimiter and null string</entry>
+ <entry><literal>exolode('1,2,3,,5', ',')</literal></entry>
+ <entry><literal>{1,2,3,4,NULL,5}</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
+ <function>implode</function>(<type>anyarray</type>, <type>text</type> <optional>, <type>text</type></optional>)
+ </literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>concatenates array elements using supplied delimiter and null string</entry>
+ <entry><literal>implode(ARRAY[1, 2, 3, NULL, 5], ',', '*')</literal></entry>
+ <entry><literal>1,2,3,*,5</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
<function>string_to_array</function>(<type>text</type>, <type>text</type>)
</literal>
</entry>
*** ./src/backend/catalog/system_views.sql.orig 2010-07-23 21:18:04.806852641 +0200
--- ./src/backend/catalog/system_views.sql 2010-07-23 22:03:56.329687977 +0200
***************
*** 487,489 ****
--- 487,497 ----
CREATE OR REPLACE FUNCTION
pg_start_backup(label text, fast boolean DEFAULT false)
RETURNS text STRICT VOLATILE LANGUAGE internal AS 'pg_start_backup';
+
+ CREATE OR REPLACE FUNCTION
+ implode(v anyarray, fldsep text, null_string text DEFAULT '')
+ RETURNS text STABLE LANGUAGE internal AS 'implode';
+
+ CREATE OR REPLACE FUNCTION
+ explode(inputstr text, fldsep text, null_string text DEFAULT '')
+ RETURNS text[] IMMUTABLE LANGUAGE internal AS 'explode';
*** ./src/backend/utils/adt/array_userfuncs.c.orig 2010-07-23 21:18:04.880689496 +0200
--- ./src/backend/utils/adt/array_userfuncs.c 2010-07-23 21:18:36.467693435 +0200
***************
*** 407,415 ****
--- 407,417 ----
create_singleton_array(FunctionCallInfo fcinfo,
Oid element_type,
Datum element,
+ bool isNull,
int ndims)
{
Datum dvalues[1];
+ bool nulls[1];
int16 typlen;
bool typbyval;
char typalign;
***************
*** 429,434 ****
--- 431,437 ----
ndims, MAXDIM)));
dvalues[0] = element;
+ nulls[0] = isNull;
for (i = 0; i < ndims; i++)
{
***************
*** 462,468 ****
typbyval = my_extra->typbyval;
typalign = my_extra->typalign;
! return construct_md_array(dvalues, NULL, ndims, dims, lbs, element_type,
typlen, typbyval, typalign);
}
--- 465,471 ----
typbyval = my_extra->typbyval;
typalign = my_extra->typalign;
! return construct_md_array(dvalues, nulls, ndims, dims, lbs, element_type,
typlen, typbyval, typalign);
}
*** ./src/backend/utils/adt/varlena.c.orig 2010-07-23 21:18:04.911693316 +0200
--- ./src/backend/utils/adt/varlena.c 2010-07-23 22:00:47.832690000 +0200
***************
*** 75,80 ****
--- 75,83 ----
static bytea *bytea_overlay(bytea *t1, bytea *t2, int sp, int sl);
static StringInfo makeStringAggState(FunctionCallInfo fcinfo);
+ static Datum _explode(PG_FUNCTION_ARGS);
+ static Datum _implode(PG_FUNCTION_ARGS);
+
/*****************************************************************************
* CONVERSION ROUTINES EXPORTED FOR USE BY C CODE *
***************
*** 2965,2970 ****
--- 2968,2984 ----
}
/*
+ * Returns true when two text params are same.
+ */
+ static
+ bool text_isequal(text *txt1, text *txt2)
+ {
+ return DatumGetBool(DirectFunctionCall2(texteq,
+ PointerGetDatum(txt1),
+ PointerGetDatum(txt2)));
+ }
+
+ /*
* text_to_array
* parse input string
* return text array of elements
***************
*** 2973,3076 ****
Datum
text_to_array(PG_FUNCTION_ARGS)
{
! text *inputstring = PG_GETARG_TEXT_PP(0);
! text *fldsep = PG_GETARG_TEXT_PP(1);
int inputstring_len;
- int fldsep_len;
- TextPositionState state;
- int fldnum;
- int start_posn;
- int end_posn;
- int chunk_len;
char *start_ptr;
text *result_text;
ArrayBuildState *astate = NULL;
! text_position_setup(inputstring, fldsep, &state);
!
! /*
! * Note: we check the converted string length, not the original, because
! * they could be different if the input contained invalid encoding.
! */
! inputstring_len = state.len1;
! fldsep_len = state.len2;
!
! /* return NULL for empty input string */
! if (inputstring_len < 1)
! {
! text_position_cleanup(&state);
PG_RETURN_NULL();
! }
! /*
! * empty field separator return one element, 1D, array using the input
! * string
*/
! if (fldsep_len < 1)
! {
! text_position_cleanup(&state);
! PG_RETURN_ARRAYTYPE_P(create_singleton_array(fcinfo, TEXTOID,
! PointerGetDatum(inputstring), 1));
! }
!
! start_posn = 1;
! /* start_ptr points to the start_posn'th character of inputstring */
! start_ptr = VARDATA_ANY(inputstring);
!
! for (fldnum = 1;; fldnum++) /* field number is 1 based */
{
! CHECK_FOR_INTERRUPTS();
!
! end_posn = text_position_next(start_posn, &state);
! if (end_posn == 0)
{
! /* fetch last field */
! chunk_len = ((char *) inputstring + VARSIZE_ANY(inputstring)) - start_ptr;
}
! else
{
! /* fetch non-last field */
! chunk_len = charlen_to_bytelen(start_ptr, end_posn - start_posn);
}
! /* must build a temp text datum to pass to accumArrayResult */
! result_text = cstring_to_text_with_len(start_ptr, chunk_len);
! /* stash away this field */
! astate = accumArrayResult(astate,
PointerGetDatum(result_text),
! false,
! TEXTOID,
! CurrentMemoryContext);
! pfree(result_text);
! if (end_posn == 0)
! break;
! start_posn = end_posn;
! start_ptr += chunk_len;
! start_posn += fldsep_len;
! start_ptr += charlen_to_bytelen(start_ptr, fldsep_len);
}
! text_position_cleanup(&state);
! PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate,
! CurrentMemoryContext));
}
/*
* array_to_text
* concatenate Cstring representation of input array elements
! * using provided field separator
*/
Datum
array_to_text(PG_FUNCTION_ARGS)
{
! ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
! char *fldsep = text_to_cstring(PG_GETARG_TEXT_PP(1));
int nitems,
*dims,
ndims;
--- 2987,3197 ----
Datum
text_to_array(PG_FUNCTION_ARGS)
{
! text *inputstr = PG_GETARG_TEXT_PP(0);
!
! /* function string_to_array returns a NULL for empty string */
! if (VARSIZE_ANY_EXHDR(inputstr) < 1)
! PG_RETURN_NULL();
!
! return _explode(fcinfo);
! }
!
! /*
! * just only outer wrapper
! */
! Datum
! explode(PG_FUNCTION_ARGS)
! {
! return _explode(fcinfo);
! }
!
! /*
! * _to_array - common code for to_array and text_to_array functions
! */
! static Datum
! _explode(PG_FUNCTION_ARGS)
! {
int inputstring_len;
char *start_ptr;
text *result_text;
ArrayBuildState *astate = NULL;
+ bool is_null;
+ text *inputstring;
+ text *fldsep;
+ text *null_string;
! /* when input string is NULL, then result is NULL too */
! if (PG_ARGISNULL(0))
PG_RETURN_NULL();
!
! inputstring = PG_GETARG_TEXT_PP(0);
! /*
! * when null string is NULL, there are not string representation for NULL,
! * the behave is compatible old text_to_array function. to_array function has
! * three arguments - last argument has default value. text_to_array function
! * has two arguments. When null_string is NULL, then behave is same like
! * old text_to_array (string_to_array) function.
*/
! if (PG_NARGS() > 2)
! null_string = (!PG_ARGISNULL(2)) ? PG_GETARG_TEXT_PP(2) : NULL;
! else
! null_string = NULL;
!
! /*
! * when separator is NULL, then returns every character as field,
! * there are no any character used as separator - etc separator is
! * space between chars.
! */
! if (!PG_ARGISNULL(1))
{
! TextPositionState state;
! int fldnum;
! int start_posn;
! int end_posn;
! int chunk_len;
!
! fldsep = PG_GETARG_TEXT_PP(1);
!
! text_position_setup(inputstring, fldsep, &state);
! /* return empty array for empty input string */
! if (state.len1 < 1)
{
! text_position_cleanup(&state);
! PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
}
!
! /*
! * empty field separator return one element, 1D, array using the input
! * string
! */
! if (state.len2 < 1)
{
! text_position_cleanup(&state);
! /* single element can be a NULL too */
! is_null = (null_string != NULL) ? text_isequal(null_string, inputstring) : false;
! PG_RETURN_ARRAYTYPE_P(create_singleton_array(fcinfo, TEXTOID,
! PointerGetDatum(inputstring),
! is_null, 1));
}
+
+ start_posn = 1;
+ /* start_ptr points to the start_posn'th character of inputstring */
+ start_ptr = VARDATA_ANY(inputstring);
+
+ for (fldnum = 1;; fldnum++) /* field number is 1 based */
+ {
+ CHECK_FOR_INTERRUPTS();
! end_posn = text_position_next(start_posn, &state);
!
! if (end_posn == 0)
! {
! /* fetch last field */
! chunk_len = ((char *) inputstring + VARSIZE_ANY(inputstring)) - start_ptr;
! }
! else
! {
! /* fetch non-last field */
! chunk_len = charlen_to_bytelen(start_ptr, end_posn - start_posn);
! }
! /* must build a temp text datum to pass to accumArrayResult */
! result_text = cstring_to_text_with_len(start_ptr, chunk_len);
! is_null = (null_string != NULL) ? text_isequal(null_string, result_text) : false;
!
! /* stash away this field */
! astate = accumArrayResult(astate,
PointerGetDatum(result_text),
! is_null,
! TEXTOID,
! CurrentMemoryContext);
! pfree(result_text);
! if (end_posn == 0)
! break;
! start_posn = end_posn;
! start_ptr += chunk_len;
! start_posn += state.len2;
! start_ptr += charlen_to_bytelen(start_ptr, state.len2);
! }
!
! text_position_cleanup(&state);
}
+ else
+ {
+ /*
+ * simple mode
+ * when field separator is NULL, then divide chars from string.
+ */
+ inputstring_len = VARSIZE_ANY_EXHDR(inputstring);
+
+ /* returns empty array for empty string */
+ if (inputstring_len < 1)
+ PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
+
+ start_ptr = VARDATA_ANY(inputstring);
+
+ while (inputstring_len > 0)
+ {
+ int chunk_len;
+
+ chunk_len = pg_mblen(start_ptr);
+ result_text = cstring_to_text_with_len(start_ptr, chunk_len);
+
+ is_null = (null_string != NULL) ? text_isequal(null_string, result_text) : false;
+
+ /* stash away this field */
+ astate = accumArrayResult(astate,
+ PointerGetDatum(result_text),
+ is_null,
+ TEXTOID,
+ CurrentMemoryContext);
! pfree(result_text);
! start_ptr += chunk_len;
! inputstring_len -= chunk_len;
! }
! }
! PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate, CurrentMemoryContext));
}
/*
* array_to_text
* concatenate Cstring representation of input array elements
! * using provided field separator - just only wrapper for
! * strict version of function.
*/
Datum
array_to_text(PG_FUNCTION_ARGS)
{
! return _implode(fcinfo);
! }
!
! /*
! * implode
! * concatenate Cstring representation of input array elements
! * using provided field separator and null string - just only
! * wrapper for non strict three params function.
! */
! Datum
! implode(PG_FUNCTION_ARGS)
! {
! return _implode(fcinfo);
! }
!
! /*
! * common code for array_to_text and to_string function. null_string can be
! * NULL (only for array_to_text function).
! */
! static Datum
! _implode(PG_FUNCTION_ARGS)
! {
! ArrayType *v;
int nitems,
*dims,
ndims;
***************
*** 3085,3090 ****
--- 3206,3241 ----
int bitmask;
int i;
ArrayMetaState *my_extra;
+ char *fieldsep;
+ char *null_string;
+ text *result;
+
+
+ /* returns NULL when input parameter is NULL */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ v = PG_GETARG_ARRAYTYPE_P(0);
+
+
+ /* field separator cannot be a NULL */
+ if (PG_ARGISNULL(1))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("field separator cannot be NULL")));
+
+ fieldsep = text_to_cstring(PG_GETARG_TEXT_PP(1));
+
+ /* get a NULL string */
+ if (PG_NARGS() > 2)
+ {
+ if (!PG_ARGISNULL(2))
+ null_string = text_to_cstring(PG_GETARG_TEXT_PP(2));
+ else
+ null_string = NULL;
+ }
+ else
+ null_string = NULL;
ndims = ARR_NDIM(v);
dims = ARR_DIMS(v);
***************
*** 3092,3098 ****
/* if there are no elements, return an empty string */
if (nitems == 0)
! PG_RETURN_TEXT_P(cstring_to_text(""));
element_type = ARR_ELEMTYPE(v);
initStringInfo(&buf);
--- 3243,3249 ----
/* if there are no elements, return an empty string */
if (nitems == 0)
! PG_RETURN_TEXT_P(cstring_to_text_with_len("", 0));
element_type = ARR_ELEMTYPE(v);
initStringInfo(&buf);
***************
*** 3140,3146 ****
/* Get source element, checking for NULL */
if (bitmap && (*bitmap & bitmask) == 0)
{
! /* we ignore nulls */
}
else
{
--- 3291,3305 ----
/* Get source element, checking for NULL */
if (bitmap && (*bitmap & bitmask) == 0)
{
! /* we ignore nulls, when null_string isn't defined (is NULL) */
! if (null_string != NULL)
! {
! if (printed)
! appendStringInfo(&buf, "%s%s", fieldsep, null_string);
! else
! appendStringInfoString(&buf, null_string);
! printed = true;
! }
}
else
{
***************
*** 3149,3155 ****
value = OutputFunctionCall(&my_extra->proc, itemvalue);
if (printed)
! appendStringInfo(&buf, "%s%s", fldsep, value);
else
appendStringInfoString(&buf, value);
printed = true;
--- 3308,3314 ----
value = OutputFunctionCall(&my_extra->proc, itemvalue);
if (printed)
! appendStringInfo(&buf, "%s%s", fieldsep, value);
else
appendStringInfoString(&buf, value);
printed = true;
***************
*** 3169,3176 ****
}
}
}
! PG_RETURN_TEXT_P(cstring_to_text_with_len(buf.data, buf.len));
}
#define HEXBASE 16
--- 3328,3338 ----
}
}
}
+
+ result = cstring_to_text_with_len(buf.data, buf.len);
+ pfree(buf.data);
! PG_RETURN_TEXT_P(result);
}
#define HEXBASE 16
*** ./src/include/catalog/pg_proc.h.orig 2010-07-23 21:18:04.970977412 +0200
--- ./src/include/catalog/pg_proc.h 2010-07-23 21:53:09.567690848 +0200
***************
*** 1022,1027 ****
--- 1022,1031 ----
DESCR("split delimited text into text[]");
DATA(insert OID = 395 ( array_to_string PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "2277 25" _null_ _null_ _null_ _null_ array_to_text _null_ _null_ _null_ ));
DESCR("concatenate array elements, using delimiter, into text");
+ DATA(insert OID = 950 ( explode PGNSP PGUID 12 1 0 0 f f f f f i 3 0 1009 "25 25 25" _null_ _null_ _null_ _null_ explode _null_ _null_ _null_ ));
+ DESCR("split delimited text into text[], possible to set null string");
+ DATA(insert OID = 951 ( implode PGNSP PGUID 12 1 0 0 f f f f f i 3 0 25 "2277 25 25" _null_ _null_ _null_ _null_ implode _null_ _null_ _null_ ));
+ DESCR("concatenate array elements, using delimiter, into text, possible to set null string");
DATA(insert OID = 515 ( array_larger PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_larger _null_ _null_ _null_ ));
DESCR("larger of two");
DATA(insert OID = 516 ( array_smaller PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_smaller _null_ _null_ _null_ ));
*** ./src/include/utils/array.h.orig 2010-07-23 21:18:05.025427635 +0200
--- ./src/include/utils/array.h 2010-07-23 21:18:36.532691416 +0200
***************
*** 274,279 ****
--- 274,280 ----
extern ArrayType *create_singleton_array(FunctionCallInfo fcinfo,
Oid element_type,
Datum element,
+ bool isNull,
int ndims);
extern Datum array_agg_transfn(PG_FUNCTION_ARGS);
*** ./src/include/utils/builtins.h.orig 2010-07-22 03:22:35.000000000 +0200
--- ./src/include/utils/builtins.h 2010-07-23 21:52:27.718688550 +0200
***************
*** 716,721 ****
--- 716,723 ----
extern Datum split_text(PG_FUNCTION_ARGS);
extern Datum text_to_array(PG_FUNCTION_ARGS);
extern Datum array_to_text(PG_FUNCTION_ARGS);
+ extern Datum exlode(PG_FUNCTION_ARGS);
+ extern Datum implode(PG_FUNCTION_ARGS);
extern Datum to_hex32(PG_FUNCTION_ARGS);
extern Datum to_hex64(PG_FUNCTION_ARGS);
extern Datum md5_text(PG_FUNCTION_ARGS);
*** ./src/test/regress/expected/arrays.out.orig 2010-07-23 21:18:05.106040250 +0200
--- ./src/test/regress/expected/arrays.out 2010-07-23 22:06:16.000000000 +0200
***************
*** 1208,1210 ****
--- 1208,1297 ----
[5:5]={"(42,43)"}
(1 row)
+ -- check to_string and to_array functions
+ select explode('abc','');
+ explode
+ ---------
+ {abc}
+ (1 row)
+
+ select explode('abc','','abc');
+ explode
+ ---------
+ {NULL}
+ (1 row)
+
+ select explode('abc',',');
+ explode
+ ---------
+ {abc}
+ (1 row)
+
+ select explode('abc',',','abc');
+ explode
+ ---------
+ {NULL}
+ (1 row)
+
+ select explode('1,2,3,4,,6',',');
+ explode
+ ------------------
+ {1,2,3,4,NULL,6}
+ (1 row)
+
+ select explode('1,2,3,4,,6',',','');
+ explode
+ ------------------
+ {1,2,3,4,NULL,6}
+ (1 row)
+
+ select explode('1,2,3,4,*,6',',','*');
+ explode
+ ------------------
+ {1,2,3,4,NULL,6}
+ (1 row)
+
+ select explode(NULL, ',') is NULL;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select explode('', ',');
+ explode
+ ---------
+ {}
+ (1 row)
+
+ select implode(NULL::int4[], ',') is NULL;
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select implode('{}'::int4[], ',');
+ implode
+ ---------
+
+ (1 row)
+
+ select implode(array[1,2,3,4,NULL,6],',');
+ implode
+ ------------
+ 1,2,3,4,,6
+ (1 row)
+
+ select implode(array[1,2,3,4,NULL,6],',','*');
+ implode
+ -------------
+ 1,2,3,4,*,6
+ (1 row)
+
+ select implode(array[1,2,3,4,NULL,6],NULL); -- should fail
+ ERROR: field separator cannot be NULL
+ select implode(array[1,2,3,4,NULL,6],',', NULL); -- ignore NULL value
+ implode
+ -----------
+ 1,2,3,4,6
+ (1 row)
+
*** ./src/test/regress/sql/arrays.sql.orig 2010-07-23 21:18:05.123458199 +0200
--- ./src/test/regress/sql/arrays.sql 2010-07-23 21:55:46.646687842 +0200
***************
*** 412,414 ****
--- 412,432 ----
select * from t1;
update t1 set f1[5].q2 = 43;
select * from t1;
+
+ -- check to_string and to_array functions
+
+ select explode('abc','');
+ select explode('abc','','abc');
+ select explode('abc',',');
+ select explode('abc',',','abc');
+ select explode('1,2,3,4,,6',',');
+ select explode('1,2,3,4,,6',',','');
+ select explode('1,2,3,4,*,6',',','*');
+ select explode(NULL, ',') is NULL;
+ select explode('', ',');
+ select implode(NULL::int4[], ',') is NULL;
+ select implode('{}'::int4[], ',');
+ select implode(array[1,2,3,4,NULL,6],',');
+ select implode(array[1,2,3,4,NULL,6],',','*');
+ select implode(array[1,2,3,4,NULL,6],NULL); -- should fail
+ select implode(array[1,2,3,4,NULL,6],',', NULL); -- ignore NULL value
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
