po 24. 8. 2020 v 4:19 odesÃlatel Peter Smith <smithpb2...@gmail.com> napsal:
> I have re-checked the string_to_table_20200821.patch.
>
> Below is one remaining problem.
>
> ====
>
> COMMENT (help text)
>
> + Splits the <parameter>string</parameter> at occurrences
> + of <parameter>delimiter</parameter> and forms the remaining data
> + into a table with one <type>text</type> type column.
> + If <parameter>delimiter</parameter> is <literal>NULL</literal>,
> + each character in the <parameter>string</parameter> will become a
> + separate element in the array.
>
> Seems like here is a cut/paste error from the string_to_array help text.
>
> "separate element in the array" should say "separate row of the table"
>
fixed
> ====
>
> >>> Maybe a different choice of function name would be more consistent
> >>> with what is already there?
> >>> e.g. split_to_table, string_split_to_table, etc.
> >>
> >> I don't agree. This function is twin (with almost identical behaviour)
> for "string_to_array" function, so I think so the name is correct.
>
> OK
>
> ====
>
please, check attached patch
Regards
Pavel
> Kind Regards,
> Peter Smith.
> Fujitsu Australia
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 51ec5281c0..ec8c70fce6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3458,6 +3458,36 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>string_to_table</primary>
+ </indexterm>
+ <function>string_to_table</function> ( <parameter>string</parameter> <type>text</type>, <parameter>delimiter</parameter> <type>text</type> <optional>, <parameter>nullstr</parameter> <type>text</type> </optional> )
+ <returnvalue>set of text</returnvalue>
+ </para>
+ <para>
+ Splits the <parameter>string</parameter> at occurrences
+ of <parameter>delimiter</parameter> and forms the remaining data
+ into a table with one <type>text</type> type column.
+ If <parameter>delimiter</parameter> is <literal>NULL</literal>,
+ each character in the <parameter>string</parameter> will become a
+ separate row of table.
+ If <parameter>delimiter</parameter> is an empty string, then
+ the <parameter>string</parameter> is treated as a single field.
+ If <parameter>null_string</parameter> is supplied and is
+ not <literal>NULL</literal>, fields matching that string are converted
+ to <literal>NULL</literal> entries.
+ </para>
+ <para>
+ <literal>string_to_table('xx~^~yy~^~zz', '~^~', 'yy')</literal>
+ <returnvalue></returnvalue>
+<programlisting>xx
+NULL
+zz</programlisting>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index df10bfb906..14213c3318 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -26,6 +26,7 @@
#include "lib/hyperloglog.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/execnodes.h"
#include "parser/scansup.h"
#include "port/pg_bswap.h"
#include "regex/regex.h"
@@ -35,6 +36,7 @@
#include "utils/memutils.h"
#include "utils/pg_locale.h"
#include "utils/sortsupport.h"
+#include "utils/tuplestore.h"
#include "utils/varlena.h"
@@ -92,6 +94,16 @@ typedef struct
pg_locale_t locale;
} VarStringSortSupport;
+/*
+ * Holds target metadata used for split string to array or to table.
+ */
+typedef struct
+{
+ ArrayBuildState *astate;
+ Tuplestorestate *tupstore;
+ TupleDesc tupdesc;
+} SplitStringTargetData;
+
/*
* This should be large enough that most strings will fit, but small enough
* that we feel comfortable putting it on the stack
@@ -139,7 +151,7 @@ static bytea *bytea_substring(Datum str,
bool length_not_specified);
static bytea *bytea_overlay(bytea *t1, bytea *t2, int sp, int sl);
static void appendStringInfoText(StringInfo str, const text *t);
-static Datum text_to_array_internal(PG_FUNCTION_ARGS);
+static bool text_to_array_internal(FunctionCallInfo fcinfo, SplitStringTargetData *tstate);
static text *array_to_text_internal(FunctionCallInfo fcinfo, ArrayType *v,
const char *fldsep, const char *null_string);
static StringInfo makeStringAggState(FunctionCallInfo fcinfo);
@@ -4679,7 +4691,19 @@ text_isequal(text *txt1, text *txt2, Oid collid)
Datum
text_to_array(PG_FUNCTION_ARGS)
{
- return text_to_array_internal(fcinfo);
+ SplitStringTargetData tstate;
+
+ /* reset tstate */
+ memset(&tstate, 0, sizeof(tstate));
+
+ if (!text_to_array_internal(fcinfo, &tstate))
+ PG_RETURN_NULL();
+
+ if (tstate.astate == NULL)
+ PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
+
+ PG_RETURN_ARRAYTYPE_P(makeArrayResult(tstate.astate,
+ CurrentMemoryContext));
}
/*
@@ -4693,16 +4717,103 @@ text_to_array(PG_FUNCTION_ARGS)
Datum
text_to_array_null(PG_FUNCTION_ARGS)
{
- return text_to_array_internal(fcinfo);
+ return text_to_array(fcinfo);
}
/*
- * common code for text_to_array and text_to_array_null functions
+ * text_to_table
+ * Parse input string and returns substrings as a table.
+ */
+Datum
+text_to_table(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+ SplitStringTargetData tstate;
+ MemoryContext old_cxt;
+
+ /* check to see if caller supports us returning a tuplestore */
+ if (rsi == NULL || !IsA(rsi, ReturnSetInfo))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("set-valued function called in context that cannot accept a set")));
+
+ if (!(rsi->allowedModes & SFRM_Materialize))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("materialize mode required, but it is not "
+ "allowed in this context")));
+
+ old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+ tstate.astate = NULL;
+ tstate.tupdesc = CreateTupleDescCopy(rsi->expectedDesc);
+ tstate.tupstore = tuplestore_begin_heap(rsi->allowedModes & SFRM_Materialize_Random,
+ false, work_mem);
+
+ MemoryContextSwitchTo(old_cxt);
+
+ (void) text_to_array_internal(fcinfo, &tstate);
+
+ tuplestore_donestoring(tstate.tupstore);
+
+ rsi->returnMode = SFRM_Materialize;
+ rsi->setResult = tstate.tupstore;
+ rsi->setDesc = tstate.tupdesc;
+
+ return (Datum) 0;
+}
+
+Datum
+text_to_table_null(PG_FUNCTION_ARGS)
+{
+ return text_to_table(fcinfo);
+}
+
+/*
+ * Add text to result set (table or array). When a result set is expected,
+ * then we fill a tuplestore, else we prepare an array.
+ */
+static void
+accum_result(SplitStringTargetData *tstate,
+ text *result_text,
+ bool is_null)
+{
+ if (tstate->tupdesc)
+ {
+ Datum values[1];
+ bool nulls[1];
+
+ /* ensure tupdesc has only one field */
+ Assert(tstate->tupdesc->natts == 1);
+
+ values[0] = PointerGetDatum(result_text);
+ nulls[0] = is_null;
+
+ tuplestore_putvalues(tstate->tupstore,
+ tstate->tupdesc,
+ values,
+ nulls);
+ }
+ else
+ {
+ tstate->astate = accumArrayResult(tstate->astate,
+ PointerGetDatum(result_text),
+ is_null,
+ TEXTOID,
+ CurrentMemoryContext);
+ }
+}
+
+/*
+ * common code for text_to_array, text_to_array_null, text_to_table and
+ * text_to_table_nulls functions
*
* These are not strict so we have to test for null inputs explicitly.
+ * Returns false, when result is null, else returns true.
+ *
*/
-static Datum
-text_to_array_internal(PG_FUNCTION_ARGS)
+static bool
+text_to_array_internal(FunctionCallInfo fcinfo, SplitStringTargetData *tstate)
{
text *inputstring;
text *fldsep;
@@ -4712,11 +4823,10 @@ text_to_array_internal(PG_FUNCTION_ARGS)
char *start_ptr;
text *result_text;
bool is_null;
- ArrayBuildState *astate = NULL;
/* when input string is NULL, then result is NULL too */
if (PG_ARGISNULL(0))
- PG_RETURN_NULL();
+ return false;
inputstring = PG_GETARG_TEXT_PP(0);
@@ -4745,7 +4855,7 @@ text_to_array_internal(PG_FUNCTION_ARGS)
/* return empty array for empty input string */
if (inputstring_len < 1)
- PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
+ return true;
/*
* empty field separator: return the input string as a one-element
@@ -4753,22 +4863,11 @@ text_to_array_internal(PG_FUNCTION_ARGS)
*/
if (fldsep_len < 1)
{
- Datum elems[1];
- bool nulls[1];
- int dims[1];
- int lbs[1];
-
/* single element can be a NULL too */
is_null = null_string ? text_isequal(inputstring, null_string, PG_GET_COLLATION()) : false;
- elems[0] = PointerGetDatum(inputstring);
- nulls[0] = is_null;
- dims[0] = 1;
- lbs[0] = 1;
- /* XXX: this hardcodes assumptions about the text type */
- PG_RETURN_ARRAYTYPE_P(construct_md_array(elems, nulls,
- 1, dims, lbs,
- TEXTOID, -1, false, TYPALIGN_INT));
+ accum_result(tstate, inputstring, is_null);
+ return true;
}
text_position_setup(inputstring, fldsep, PG_GET_COLLATION(), &state);
@@ -4802,12 +4901,7 @@ text_to_array_internal(PG_FUNCTION_ARGS)
is_null = null_string ? text_isequal(result_text, null_string, PG_GET_COLLATION()) : false;
/* stash away this field */
- astate = accumArrayResult(astate,
- PointerGetDatum(result_text),
- is_null,
- TEXTOID,
- CurrentMemoryContext);
-
+ accum_result(tstate, result_text, is_null);
pfree(result_text);
if (!found)
@@ -4844,12 +4938,7 @@ text_to_array_internal(PG_FUNCTION_ARGS)
is_null = null_string ? text_isequal(result_text, null_string, PG_GET_COLLATION()) : false;
/* stash away this field */
- astate = accumArrayResult(astate,
- PointerGetDatum(result_text),
- is_null,
- TEXTOID,
- CurrentMemoryContext);
-
+ accum_result(tstate, result_text, is_null);
pfree(result_text);
start_ptr += chunk_len;
@@ -4857,8 +4946,7 @@ text_to_array_internal(PG_FUNCTION_ARGS)
}
}
- PG_RETURN_ARRAYTYPE_P(makeArrayResult(astate,
- CurrentMemoryContext));
+ return true;
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 27989971db..b71d6748bc 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3561,6 +3561,14 @@
{ oid => '2768', descr => 'split string by pattern',
proname => 'regexp_split_to_array', prorettype => '_text',
proargtypes => 'text text text', prosrc => 'regexp_split_to_array' },
+{ oid => '8432', descr => 'split delimited text',
+ proname => 'string_to_table', proisstrict => 'f', prorows => '1000',
+ proretset => 't', prorettype => 'text', proargtypes => 'text text',
+ prosrc => 'text_to_table' },
+{ oid => '8433', descr => 'split delimited text with null string',
+ proname => 'string_to_table', proisstrict => 'f', prorows => '1000',
+ proretset => 't', prorettype => 'text', proargtypes => 'text text text',
+ prosrc => 'text_to_table_null' },
{ oid => '2089', descr => 'convert int4 number to hex',
proname => 'to_hex', prorettype => 'text', proargtypes => 'int4',
prosrc => 'to_hex32' },
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index c730563f03..8b7a7adee3 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1755,6 +1755,965 @@ select string_to_array('1,2,3,4,*,6', ',', '*');
{1,2,3,4,NULL,6}
(1 row)
+select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v);
+ v | is null
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v);
+ v | is null
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v);
+ v | is null
+-----+---------
+ 1 | f
+ 2|3 | f
+ | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3', '') g(v);
+ v | is null
+-------+---------
+ 1|2|3 | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('', '|') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v);
+ v | is null
+---+---------
+ 1 | f
+ | | f
+ 2 | f
+ | | f
+ 3 | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('abc', '') g(v);
+ v | is null
+-----+---------
+ abc | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v);
+ v | is null
+---+---------
+ | t
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', ',') g(v);
+ v | is null
+-----+---------
+ abc | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v);
+ v | is null
+---+---------
+ | t
+(1 row)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v);
+ v | is null
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+ | f
+ 6 | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v) g(v);
+ERROR: syntax error at or near "g"
+LINE 1: ..."is null" from string_to_table('1,2,3,4,,6', ',') g(v) g(v);
+ ^
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v);
+ v | is null
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+ | t
+ 6 | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v);
+ v | is null
+---+---------
+ 1 | f
+ 2 | f
+ 3 | f
+ 4 | f
+ | t
+ 6 | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('', '') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', '|') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '') g(v);
+ v | is null
+-------+---------
+ a|b|c | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('a|b|c', NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ | | f
+ b | f
+ | | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|', '|') g(v);
+ v | is null
+---+---------
+ | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '|') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|abc', '|') g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('abc|', '|') g(v);
+ v | is null
+-----+---------
+ abc | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab||c', '|') g(v);
+ v | is null
+----+---------
+ ab | f
+ | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c', '|') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c|', '|') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+ | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c', '|') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('|abc|', '|') g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+ | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c', '|') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c|', '|') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|||ab|||','||') g(v);
+ v | is null
+-----+---------
+ | f
+ |ab | f
+ | | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('', '',NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', NULL,NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', '|',NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '',NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, NULL,NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|',NULL) g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '',NULL) g(v);
+ v | is null
+-------+---------
+ a|b|c | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ | | f
+ b | f
+ | | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|', '|',NULL) g(v);
+ v | is null
+---+---------
+ | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '|',NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|abc', '|',NULL) g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('abc|', '|',NULL) g(v);
+ v | is null
+-----+---------
+ abc | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab||c', '|',NULL) g(v);
+ v | is null
+----+---------
+ ab | f
+ | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c', '|',NULL) g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c|', '|',NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+ | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c', '|',NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('|abc|', '|',NULL) g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+ | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|',NULL) g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c', '|',NULL) g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c|', '|',NULL) g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|',NULL) g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|||ab|||','||',NULL) g(v);
+ v | is null
+-----+---------
+ | f
+ |ab | f
+ | | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('', '','') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', NULL,'') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', '|','') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '','') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, NULL,'') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|','') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '','') g(v);
+ v | is null
+-------+---------
+ a|b|c | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,'') g(v);
+ v | is null
+---+---------
+ a | f
+ | | f
+ b | f
+ | | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|', '|','') g(v);
+ v | is null
+---+---------
+ | t
+ | t
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '|','') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|abc', '|','') g(v);
+ v | is null
+-----+---------
+ | t
+ abc | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('abc|', '|','') g(v);
+ v | is null
+-----+---------
+ abc | f
+ | t
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab||c', '|','') g(v);
+ v | is null
+----+---------
+ ab | f
+ | t
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c', '|','') g(v);
+ v | is null
+---+---------
+ | t
+ a | f
+ b | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c|', '|','') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+ | t
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c', '|','') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('|abc|', '|','') g(v);
+ v | is null
+-----+---------
+ | t
+ abc | f
+ | t
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|','') g(v);
+ v | is null
+---+---------
+ | t
+ a | f
+ b | f
+ c | f
+ | t
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c', '|','') g(v);
+ v | is null
+---+---------
+ | t
+ a | f
+ b | f
+ | t
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c|', '|','') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ c | f
+ | t
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|','') g(v);
+ v | is null
+---+---------
+ | t
+ a | f
+ b | f
+ | t
+ c | f
+ | t
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|||ab|||','||','') g(v);
+ v | is null
+-----+---------
+ | t
+ |ab | f
+ | | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('', '','*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', NULL,'*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('', '|','*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '','*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, NULL,'*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table(NULL, '|','*') g(v);
+ v | is null
+---+---------
+(0 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '','*') g(v);
+ v | is null
+-------+---------
+ a|b|c | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,'*') g(v);
+ v | is null
+---+---------
+ a | f
+ | | f
+ b | f
+ | | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|abc', '|','*') g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('abc|', '|','*') g(v);
+ v | is null
+-----+---------
+ abc | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab||c', '|','*') g(v);
+ v | is null
+----+---------
+ ab | f
+ | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|c|', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ c | f
+ | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('|abc|', '|','*') g(v);
+ v | is null
+-----+---------
+ | f
+ abc | f
+ | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b||c|', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | f
+ c | f
+ | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|||ab|||','||','*') g(v);
+ v | is null
+-----+---------
+ | f
+ |ab | f
+ | | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|*|c', '','*') g(v);
+ v | is null
+---------+---------
+ a|b|*|c | f
+(1 row)
+
+select v, v is null as "is null" from string_to_table('a|b|*|c', NULL,'*') g(v);
+ v | is null
+---+---------
+ a | f
+ | | f
+ b | f
+ | | f
+ | t
+ | | f
+ c | f
+(7 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|*|c', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ c | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('|ab*c', '|','*') g(v);
+ v | is null
+------+---------
+ | f
+ ab*c | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab*c|', '|','*') g(v);
+ v | is null
+------+---------
+ ab*c | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('ab*||c', '|','*') g(v);
+ v | is null
+-----+---------
+ ab* | f
+ | f
+ c | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|*|c','|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | t
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|*|c|', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ c | f
+ | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|*||c', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ | f
+ c | f
+(5 rows)
+
+select v, v is null as "is null" from string_to_table('|ab*c|', '|','*') g(v);
+ v | is null
+------+---------
+ | f
+ ab*c | f
+ | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|*|c|', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | t
+ c | f
+ | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|*||c', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | t
+ | f
+ c | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|*||c|', '|','*') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ | f
+ c | f
+ | f
+(6 rows)
+
+select v, v is null as "is null" from string_to_table('|a|b|*||c|', '|','*') g(v);
+ v | is null
+---+---------
+ | f
+ a | f
+ b | f
+ | t
+ | f
+ c | f
+ | f
+(7 rows)
+
+select v, v is null as "is null" from string_to_table('|||ab|||*|||','||','*') g(v);
+ v | is null
+-----+---------
+ | f
+ |ab | f
+ |* | f
+ | | f
+(4 rows)
+
+select v, v is null as "is null" from string_to_table('*','','*') g(v);
+ v | is null
+---+---------
+ | t
+(1 row)
+
+select v, v is null as "is null" from string_to_table('*','*','*') g(v);
+ v | is null
+---+---------
+ | f
+ | f
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('**',NULL,'*') g(v);
+ v | is null
+---+---------
+ | t
+ | t
+(2 rows)
+
+select v, v is null as "is null" from string_to_table('axbXcxd','x') g(v);
+ v | is null
+-----+---------
+ a | f
+ bXc | f
+ d | f
+(3 rows)
+
+select v, v is null as "is null" from string_to_table('a|b|X|x|c','|','X') g(v);
+ v | is null
+---+---------
+ a | f
+ b | f
+ | t
+ x | f
+ c | f
+(5 rows)
+
select array_to_string(NULL::int4[], ',') IS NULL;
?column?
----------
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 25dd4e2c6d..3275003c63 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -544,6 +544,136 @@ select string_to_array('1,2,3,4,,6', ',');
select string_to_array('1,2,3,4,,6', ',', '');
select string_to_array('1,2,3,4,*,6', ',', '*');
+select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v);
+select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v);
+select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v);
+select v, v is null as "is null" from string_to_table('1|2|3', '') g(v);
+select v, v is null as "is null" from string_to_table('', '|') g(v);
+select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v);
+select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
+select v, v is null as "is null" from string_to_table('abc', '') g(v);
+select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v);
+select v, v is null as "is null" from string_to_table('abc', ',') g(v);
+select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v);
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v);
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v) g(v);
+select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v);
+select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v);
+
+select v, v is null as "is null" from string_to_table('', '') g(v);
+select v, v is null as "is null" from string_to_table('', NULL) g(v);
+select v, v is null as "is null" from string_to_table('', '|') g(v);
+select v, v is null as "is null" from string_to_table(NULL, '') g(v);
+select v, v is null as "is null" from string_to_table(NULL, NULL) g(v);
+select v, v is null as "is null" from string_to_table(NULL, '|') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', NULL) g(v);
+select v, v is null as "is null" from string_to_table('|', '|') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '|') g(v);
+select v, v is null as "is null" from string_to_table('|abc', '|') g(v);
+select v, v is null as "is null" from string_to_table('abc|', '|') g(v);
+select v, v is null as "is null" from string_to_table('ab||c', '|') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c', '|') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c|', '|') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c', '|') g(v);
+select v, v is null as "is null" from string_to_table('|abc|', '|') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c', '|') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c|', '|') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|') g(v);
+select v, v is null as "is null" from string_to_table('|||ab|||','||') g(v);
+
+select v, v is null as "is null" from string_to_table('', '',NULL) g(v);
+select v, v is null as "is null" from string_to_table('', NULL,NULL) g(v);
+select v, v is null as "is null" from string_to_table('', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table(NULL, '',NULL) g(v);
+select v, v is null as "is null" from string_to_table(NULL, NULL,NULL) g(v);
+select v, v is null as "is null" from string_to_table(NULL, '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,NULL) g(v);
+select v, v is null as "is null" from string_to_table('|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|abc', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('abc|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('ab||c', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b|c|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b||c', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|abc|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('a|b||c|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|',NULL) g(v);
+select v, v is null as "is null" from string_to_table('|||ab|||','||',NULL) g(v);
+
+select v, v is null as "is null" from string_to_table('', '','') g(v);
+select v, v is null as "is null" from string_to_table('', NULL,'') g(v);
+select v, v is null as "is null" from string_to_table('', '|','') g(v);
+select v, v is null as "is null" from string_to_table(NULL, '','') g(v);
+select v, v is null as "is null" from string_to_table(NULL, NULL,'') g(v);
+select v, v is null as "is null" from string_to_table(NULL, '|','') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '','') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,'') g(v);
+select v, v is null as "is null" from string_to_table('|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|abc', '|','') g(v);
+select v, v is null as "is null" from string_to_table('abc|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('ab||c', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c', '|','') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|abc|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c', '|','') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|','') g(v);
+select v, v is null as "is null" from string_to_table('|||ab|||','||','') g(v);
+
+select v, v is null as "is null" from string_to_table('', '','*') g(v);
+select v, v is null as "is null" from string_to_table('', NULL,'*') g(v);
+select v, v is null as "is null" from string_to_table('', '|','*') g(v);
+select v, v is null as "is null" from string_to_table(NULL, '','*') g(v);
+select v, v is null as "is null" from string_to_table(NULL, NULL,'*') g(v);
+select v, v is null as "is null" from string_to_table(NULL, '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', NULL,'*') g(v);
+select v, v is null as "is null" from string_to_table('|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|abc', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('abc|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('ab||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|abc|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b||c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b||c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|||ab|||','||','*') g(v);
+
+select v, v is null as "is null" from string_to_table('a|b|*|c', '','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|*|c', NULL,'*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|*|c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|ab*c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('ab*c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('ab*||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|*|c','|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|*|c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|*||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|ab*c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|*|c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|*||c', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('a|b|*||c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|a|b|*||c|', '|','*') g(v);
+select v, v is null as "is null" from string_to_table('|||ab|||*|||','||','*') g(v);
+
+select v, v is null as "is null" from string_to_table('*','','*') g(v);
+select v, v is null as "is null" from string_to_table('*','*','*') g(v);
+select v, v is null as "is null" from string_to_table('**',NULL,'*') g(v);
+select v, v is null as "is null" from string_to_table('axbXcxd','x') g(v);
+select v, v is null as "is null" from string_to_table('a|b|X|x|c','|','X') g(v);
+
select array_to_string(NULL::int4[], ',') IS NULL;
select array_to_string('{}'::int4[], ',');
select array_to_string(array[1,2,3,4,NULL,6], ',');
