pá 21. 8. 2020 v 11:08 odesílatel Pavel Stehule <pavel.steh...@gmail.com> napsal:
> > > pá 21. 8. 2020 v 9:44 odesílatel Peter Smith <smithpb2...@gmail.com> > napsal: > >> On Fri, Aug 21, 2020 at 5:21 AM Pavel Stehule <pavel.steh...@gmail.com> >> wrote: >> >> > new patch attached >> >> Thanks for taking some of my previous review comments. >> >> I have re-checked the string_to_table_20200820.patch. >> >> Below are some remaining questions/comments: >> >> ==== >> >> COMMENT (help text) >> >> + Splits the <parameter>string</parameter> at occurrences >> + of <parameter>delimiter</parameter> and forms the remaining data >> + into a <type>text</type> tavke. >> >> What did you mean by "remaining" in that description? >> It gets a bit strange thinking about remaining NULLs, or remaining >> empty strings. >> >> Why not just say "... and forms the data into a <type>text</type> table." >> >> --- >> >> + Splits the <parameter>string</parameter> at occurrences >> + of <parameter>delimiter</parameter> and forms the remaining data >> + into a <type>text</type> tavke. >> >> Typo: "tavke." -> "table." >> > > This text is taken from doc for string_to_array > I fixed typo. I hope and expect so doc will be finalized by native speakers. > > >> ==== >> >> COMMENT (help text reference to regexp_split_to_table) >> >> + input <parameter>string</parameter> can be done by function >> + <function>regexp_split_to_table</function> (see <xref >> linkend="functions-posix-regexp"/>). >> + </para> >> >> In the previous review I suggested adding a reference to the >> regexp_split_to_table function. >> A hyperlink would be a bonus, but maybe it is not possible. >> >> The hyperlink added in the latest patch is to page for POSIX Regular >> Expressions, which doesn't seem appropriate. >> > > ok I remove it > >> >> ==== >> >> QUESTION (test cases) >> >> Thanks for merging lots of my additional test cases! >> >> Actually, the previous PDF I sent was 2 pages long but you only merged >> the tests of page 1. >> I wondered was it accidental to omit all those 2nd page tests? >> > > I'll check it > I forgot it - now it is merged. Maybe it is over dimensioned for one function, but it is (at the end) a test of string_to_array function too. > >> ==== >> >> QUESTION (function name?) >> >> I noticed that ALL current string functions that use delimiters have >> the word "split" in their name. >> >> e.g. >> * regexp_split_to_array >> * regexp_split_to_table >> * split_part >> >> But "string_to_table" is not following this pattern. >> >> 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. > Unfortunately - there is not consistency in naming already, But I think so string_to_table is a better name, because this function is almost identical with string_to_array. Regards Pavel > >> ==== >> >> Kind Regards, >> Peter Smith. >> Fujitsu Australia >> >
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9a4ac5a1ea..56cecff6ca 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 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. + </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..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], ',');