Hi
čt 20. 8. 2020 v 4:07 odesílatel Peter Smith <smithpb2...@gmail.com> napsal:
> Hi.
>
> I have been looking at the patch: string_to_table-20200706-2.patch
>
> Below are some review comments for your consideration.
>
> ====
>
> COMMENT func.sgml (style)
>
> + <para>
> + splits string into table using supplied delimiter and
> + optional null string.
> + </para>
>
> The format style of the short description is inconsistent with the
> other functions.
> e.g. Should start with Capital letter.
> e.g. Should tag the parameter names properly
>
> Something like:
> <para>
> Splits <parameter>string</parameter> into a table
> using supplied <parameter>delimiter</parameter>
> and optional null string <parameter>nullstr</parameter>.
> </para>
>
>
done
> ====
>
> COMMENT func.sgml (what does nullstr do)
>
> The description does not sufficiently describe the purpose/behaviour
> of the nullstr.
>
> e.g. Firstly I thought that it meant if 2 consecutive delimiters were
> encountered it would substitute this string as the row value. But it
> is doing the opposite of what I guessed - if the extracted row value
> is the same as nullstr then a NULL row is inserted instead.
>
>
done
> ====
>
> COMMENT func.sgml (wrong sample output)
>
> +<programlisting>xx
> +yy,
> +zz</programlisting>
>
> This output is incorrect for the sample given. There is no "yy," in
> the output because there is a 'yy' nullstr substitution.
>
> Should be:
> ---
> xx
> NULL
> zz
> ---
>
fixed
> ====
>
> COMMENT func.sgml (related to regexp_split_to_table)
>
> Because this new function is similar to the existing
> regexp_split_to_table, perhaps they should cross-reference each other
> so a reader of this documentation is made aware of the alternative
> function?
>
I wrote new sentence with ref
>
> ====
>
> COMMENT (test cases)
>
> It is impossible to tell difference in the output between empty
> strings and nulls currently, so maybe you can change all the tests to
> have a form like below so they can be validated properly:
>
> # select v, v IS NULL as "is null" from
> string_to_table('a,b,*,c,d,',',','*') g(v);
> v | is null
> ---+---------
> a | f
> b | f
> | t
> c | f
> d | f
> | f
> (6 rows)
>
> or maybe like this is even easier:
>
> # select quote_nullable(string_to_table('a|*||c|d|','|','*'));
> quote_nullable
> ----------------
> 'a'
> NULL
> ''
> 'c'
> 'd'
> ''
> (6 rows)
>
I prefer the first variant, it is clean. It is good idea, done
> Something similar was already proposed before [1] but that never got
> put into the test code.
> [1]
> https://www.postgresql.org/message-id/CAFj8pRDSzDYmaS06dfMXBfbr8x%2B3xjDJxA5kbL3h8%2BeOGoRUcA%40mail.gmail.com
>
> ====
>
> COMMENT (test cases)
>
> There are multiple combinations of the parameters to this function and
> MANY different results depending on different values they can take, so
> the existing tests only cover a small sample.
>
> I have attached a lot more test scenarios that you may want to include
> for better test coverage. Everything seemed to work as expected.
>
ok, merged
> PSA test-cases.pdf
>
> ====
>
> COMMENT (accum_result)
>
> + Datum values[1];
> + bool nulls[1];
> +
> + values[0] = PointerGetDatum(result_text);
> + nulls[0] = is_null;
>
> Why not use variables instead of arrays with only 1 element?
>
Technically it is equivalent, but I think so using one element array is
more correct, because function heap_form_tuple expects an array. Sure in C
language there is no difference between pointer to value or pointer to
array, but minimally the name of the argument "values" implies so argument
is an array.
This pattern is used more times in Postgres. You can find a fragments where
although we know so array has only one field, still we works with array
misc.c
hash.c
execTuples.c
but I can this code simplify little bit - I can use function
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
I see, so this code can be reduced more, and I don't need local variables,
but I prefer to be consistent with other parts, and I feel better if I pass
an array where the array is expected.
This is not extra important, and I can it change, just I think this variant
is cleaner little bit
> ====
>
> COMMENT (text_to_array_internal)
>
> + if (!tstate.astate)
> + PG_RETURN_ARRAYTYPE_P(construct_empty_array(TEXTOID));
>
> Maybe the condition is more readable when expressed as:
> if (tstate.astate == NULL)
>
>
done
new patch attached
Thank you for precious review
Regards
Pavel
====
>
> Kind Regards,
> Peter Smith.
> Fujitsu Australia
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9a4ac5a1ea..bead1b6b74 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3458,6 +3458,38 @@ 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 <type>text</type> tavke.
+ If <parameter>delimiter</parameter> is <literal>NULL</literal>,
+ each character in the <parameter>string</parameter> will become a
+ separate element in the array.
+ 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. More complex operations over
+ input <parameter>string</parameter> can be done by function
+ <function>regexp_split_to_table</function> (see <xref linkend="functions-posix-regexp"/>).
+ </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..c4a311b318 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 => '2228', descr => 'split delimited text',
+ proname => 'string_to_table', proisstrict => 'f', prorows => '1000',
+ proretset => 't', prorettype => 'text', proargtypes => 'text text',
+ prosrc => 'text_to_table' },
+{ oid => '4142', 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..312d23de73 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1755,6 +1755,619 @@ 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 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..34a2398ef7 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -544,6 +544,91 @@ 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 array_to_string(NULL::int4[], ',') IS NULL;
select array_to_string('{}'::int4[], ',');
select array_to_string(array[1,2,3,4,NULL,6], ',');
