multiset-20110106.patch
application/octet-stream
Filename: multiset-20110106.patch
Type: application/octet-stream
Part: 0
Message:
Re: MULTISET patch
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| doc/src/sgml/func.sgml | 0 | 0 |
| src/backend/nodes/makefuncs.c | 0 | 0 |
| src/backend/parser/gram.y | 0 | 0 |
| src/backend/utils/adt/array_userfuncs.c | 0 | 0 |
| src/include/catalog/pg_aggregate.h | 0 | 0 |
| src/include/catalog/pg_proc.h | 0 | 0 |
| src/include/nodes/makefuncs.h | 0 | 0 |
| src/include/parser/kwlist.h | 0 | 0 |
| src/include/utils/array.h | 0 | 0 |
| src/test/regress/expected/arrays.out | 0 | 0 |
| src/test/regress/sql/arrays.sql | 0 | 0 |
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d177775..fa004a9 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT NULLIF(value, '(none)') ...
*** 10173,10178 ****
--- 10173,10251 ----
<entry><literal>ARRAY[4,5,6] || 7</literal></entry>
<entry><literal>{4,5,6,7}</literal></entry>
</row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>IS A SET</primary>
+ </indexterm>
+ <literal>IS [ NOT ] A SET</literal>
+ </entry>
+ <entry>has only unique elements</entry>
+ <entry><literal>ARRAY[1,2,3] IS A SET</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>MEMBER OF</primary>
+ </indexterm>
+ <literal>[ NOT ] MEMBER OF</literal>
+ </entry>
+ <entry>is a member of</entry>
+ <entry><literal>2 MEMBER OF ARRAY[1,2,3]</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>SUBMULTISET OF</primary>
+ </indexterm>
+ <literal>[ NOT ] SUBMULTISET OF</literal>
+ </entry>
+ <entry>is a subset of</entry>
+ <entry><literal>ARRAY[1,2] SUBMULTISET OF ARRAY[3,2,1]</literal></entry>
+ <entry><literal>t</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>MULTISET INTERSECT</primary>
+ </indexterm>
+ <literal>MULTISET INTERSECT [ ALL | DISTINCT ]</literal>
+ </entry>
+ <entry>intersection of</entry>
+ <entry><literal>ARRAY[1,1,2] MULTISET INTERSECT ARRAY[1,3]</literal></entry>
+ <entry><literal>{1}</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>MULTISET UNION</primary>
+ </indexterm>
+ <literal>MULTISET UNION [ ALL | DISTINCT ]</literal>
+ </entry>
+ <entry>union of</entry>
+ <entry><literal>ARRAY[1,1,2] MULTISET UNION ARRAY[1,3]</literal></entry>
+ <entry><literal>{1,2,3}</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>MULTISET EXCEPT</primary>
+ </indexterm>
+ <literal>MULTISET EXCEPT [ ALL | DISTINCT ]</literal>
+ </entry>
+ <entry>subtraction of</entry>
+ <entry><literal>ARRAY[1,1,2] MULTISET EXCEPT ARRAY[1,3]</literal></entry>
+ <entry><literal>{2}</literal></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
*************** SELECT NULLIF(value, '(none)') ...
*** 10191,10196 ****
--- 10264,10286 ----
</para>
<para>
+ In <literal>IS A SET</>, <literal>MEMBER OF</>, <literal>SUBMULTISET OF</>,
+ <literal>MULTISET INTERSECT</>, <literal>MULTISET UNION</>, and
+ <literal>MULTISET EXCEPT</> operators, the order of elements in input array
+ are ignored. They treats the input as a multiset (or bag) rather than an array.
+ Dimension and lower bound of the array don't affect the result at all.
+ </para>
+
+ <para>
+ <literal>SUBMULTISET OF</> treats NULLs in input arrays as unknown values.
+ For example, <literal>ARRAY[1, 2] SUBMULTISET OF ARRAY[1, NULL]</> returns
+ NULL. It means we cannot determine whether they matches or not because the
+ NULL in the right hand argument might be 2 or other value. On the other hand,
+ <literal>ARRAY[1, 2] SUBMULTISET OF ARRAY[3, NULL]</> returns false because
+ there are NULL values less than unmatched values.
+ </para>
+
+ <para>
See <xref linkend="arrays"> for more details about array operator
behavior.
</para>
*************** SELECT NULLIF(value, '(none)') ...
*** 10226,10240 ****
--- 10316,10342 ----
<primary>array_prepend</primary>
</indexterm>
<indexterm>
+ <primary>array_sort</primary>
+ </indexterm>
+ <indexterm>
<primary>array_to_string</primary>
</indexterm>
<indexterm>
<primary>array_upper</primary>
</indexterm>
<indexterm>
+ <primary>cardinality</primary>
+ </indexterm>
+ <indexterm>
<primary>string_to_array</primary>
</indexterm>
<indexterm>
+ <primary>set</primary>
+ </indexterm>
+ <indexterm>
+ <primary>trim_array</primary>
+ </indexterm>
+ <indexterm>
<primary>unnest</primary>
</indexterm>
*************** SELECT NULLIF(value, '(none)') ...
*** 10344,10349 ****
--- 10446,10462 ----
<row>
<entry>
<literal>
+ <function>array_sort</function>(<type>anyarray</type>)
+ </literal>
+ </entry>
+ <entry><type>anyarray</type></entry>
+ <entry>sort elements in an array in ascending order</entry>
+ <entry><literal>array_sort(ARRAY[3,2,NULL,1])</literal></entry>
+ <entry><literal>{1,2,3,NULL}</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
<function>array_to_string</function>(<type>anyarray</type>, <type>text</type> <optional>, <type>text</type></optional>)
</literal>
</entry>
*************** SELECT NULLIF(value, '(none)') ...
*** 10379,10384 ****
--- 10492,10530 ----
<row>
<entry>
<literal>
+ <function>cardinality</function>(<type>anyarray</type>)
+ </literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>returns the number of elements in an array</entry>
+ <entry><literal>cardinality(ARRAY[1,2,3])</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
+ <function>set</function>(<type>anyarray</type>)
+ </literal>
+ </entry>
+ <entry><type>anyarray</type></entry>
+ <entry>remove duplicated elements in an array</entry>
+ <entry><literal>set(ARRAY[1,3,2,3,NULL,1,NULL])</literal></entry>
+ <entry><literal>{1,2,3,NULL}</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
+ <function>trim_array</function>(<type>anyarray</type>)
+ </literal>
+ </entry>
+ <entry><type>anyarray</type></entry>
+ <entry>remove elements at end of an array</entry>
+ <entry><literal>trim_array(ARRAY[1, 2, 3], 2)</literal></entry>
+ <entry><literal>{1}</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
<function>unnest</function>(<type>anyarray</type>)
</literal>
</entry>
*************** SELECT NULLIF(value, '(none)') ...
*** 10420,10427 ****
</note>
<para>
See also <xref linkend="functions-aggregate"> about the aggregate
! function <function>array_agg</function> for use with arrays.
</para>
</sect1>
--- 10566,10580 ----
</note>
<para>
+ In <function>array_sort</>, <function>set</>, and <function>trim_array</>
+ functions, input arrays are always flattened into one-dimensional arrays.
+ In addition, the lower bounds of the arrays are adjusted to 1.
+ </para>
+
+ <para>
See also <xref linkend="functions-aggregate"> about the aggregate
! function <function>array_agg</function>, <function>collect</>,
! <function>fusion</>, and <function>intersection</> for use with arrays.
</para>
</sect1>
*************** SELECT NULLIF(value, '(none)') ...
*** 10467,10473 ****
<function>array_agg(<replaceable class="parameter">expression</replaceable>)</function>
</entry>
<entry>
! any
</entry>
<entry>
array of the argument type
--- 10620,10626 ----
<function>array_agg(<replaceable class="parameter">expression</replaceable>)</function>
</entry>
<entry>
! any non-array
</entry>
<entry>
array of the argument type
*************** SELECT NULLIF(value, '(none)') ...
*** 10567,10572 ****
--- 10720,10741 ----
<row>
<entry>
<indexterm>
+ <primary>collect</primary>
+ </indexterm>
+ <function>collect(<replaceable class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ any non-array
+ </entry>
+ <entry>
+ array of the argument type
+ </entry>
+ <entry>an alias for <literal>array_agg</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>count</primary>
</indexterm>
<function>count(*)</function>
*************** SELECT NULLIF(value, '(none)') ...
*** 10605,10610 ****
--- 10774,10811 ----
<row>
<entry>
<indexterm>
+ <primary>fusion</primary>
+ </indexterm>
+ <function>fusion(<replaceable class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ any array
+ </entry>
+ <entry>
+ same as argument type
+ </entry>
+ <entry>concatenation of input arrays</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>intersection</primary>
+ </indexterm>
+ <function>intersection(<replaceable class="parameter">expression</replaceable>)</function>
+ </entry>
+ <entry>
+ any array
+ </entry>
+ <entry>
+ same as argument type
+ </entry>
+ <entry>intersection of input arrays</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>max</primary>
</indexterm>
<function>max(<replaceable class="parameter">expression</replaceable>)</function>
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 79da185..df95fee 100644
*** a/src/backend/nodes/makefuncs.c
--- b/src/backend/nodes/makefuncs.c
*************** makeFuncExpr(Oid funcid, Oid rettype, Li
*** 454,459 ****
--- 454,474 ----
}
/*
+ * makeFuncCall -
+ * build a FuncCall node
+ */
+ FuncCall *
+ makeFuncCall(List *funcname, List *args, int location)
+ {
+ FuncCall *n = makeNode(FuncCall);
+
+ n->funcname = funcname;
+ n->args = args;
+ n->location = location;
+ return n;
+ }
+
+ /*
* makeDefElem -
* build a DefElem node
*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 660947c..77fb898 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** static RangeVar *makeRangeVarFromAnyName
*** 468,474 ****
*/
/* ordinary key words in alphabetical order */
! %token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION
--- 468,474 ----
*/
/* ordinary key words in alphabetical order */
! %token <keyword> A ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER
AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC
ASSERTION ASSIGNMENT ASYMMETRIC AT ATTRIBUTE AUTHORIZATION
*************** static RangeVar *makeRangeVarFromAnyName
*** 511,517 ****
LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP
LOCATION LOCK_P LOGIN_P
! MAPPING MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
--- 511,517 ----
LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP
LOCATION LOCK_P LOGIN_P
! MAPPING MATCH MAXVALUE MEMBER MINUTE_P MINVALUE MODE MONTH_P MOVE MULTISET
NAME_P NAMES NATIONAL NATURAL NCHAR NEXT NO NOCREATEDB
NOCREATEROLE NOCREATEUSER NOINHERIT NOLOGIN_P NONE NOREPLICATION_P
*************** static RangeVar *makeRangeVarFromAnyName
*** 535,542 ****
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
! STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBSTRING SUPERUSER_P
! SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
--- 535,542 ----
SAVEPOINT SCHEMA SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETOF SHARE
SHOW SIMILAR SIMPLE SMALLINT SOME STABLE STANDALONE_P START STATEMENT
! STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P SUBMULTISET SUBSTRING
! SUPERUSER_P SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN TIME TIMESTAMP
TO TRAILING TRANSACTION TREAT TRIGGER TRIM TRUE_P
*************** static RangeVar *makeRangeVarFromAnyName
*** 605,610 ****
--- 605,611 ----
%nonassoc NOTNULL
%nonassoc ISNULL
%nonassoc IS NULL_P TRUE_P FALSE_P UNKNOWN /* sets precedence for IS NULL, etc */
+ %nonassoc MEMBER MULTISET SUBMULTISET
%left '+' '-'
%left '*' '/' '%'
%left '^'
*************** a_expr: c_expr { $$ = $1; }
*** 9584,9589 ****
--- 9585,9654 ----
list_make1($1), @2),
@2);
}
+ | a_expr IS A SET
+ {
+ $$ = (Node *) makeFuncCall(
+ SystemFuncName("is_a_set"),
+ list_make1($1), @2);
+ }
+ | a_expr IS NOT A SET
+ {
+ $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL,
+ (Node *) makeFuncCall(
+ SystemFuncName("is_a_set"),
+ list_make1($1), @2), @2);
+ }
+ | a_expr MEMBER opt_of a_expr
+ {
+ $$ = (Node *) makeSimpleA_Expr(AEXPR_OP_ANY,
+ "=", $1, $4, @2);
+ }
+ | a_expr NOT MEMBER opt_of a_expr
+ {
+ $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL,
+ (Node *) makeSimpleA_Expr(AEXPR_OP_ANY, "=",
+ $1, $5, @2), @2);
+ }
+ | a_expr SUBMULTISET opt_of a_expr
+ {
+ $$ = (Node *) makeFuncCall(
+ SystemFuncName("submultiset_of"),
+ list_make2($1, $4), @2);
+ }
+ | a_expr NOT SUBMULTISET opt_of a_expr
+ {
+ $$ = (Node *) makeA_Expr(AEXPR_NOT, NIL, NULL,
+ (Node *) makeFuncCall(
+ SystemFuncName("submultiset_of"),
+ list_make2($1, $5), @2), @2);
+ }
+ | a_expr MULTISET UNION opt_all a_expr
+ {
+ $$ = (Node *) makeFuncCall(
+ SystemFuncName("multiset_union"),
+ list_make3($1, $5, makeBoolAConst($4, -1)), @2);
+ }
+ | a_expr MULTISET INTERSECT opt_all a_expr
+ {
+ $$ = (Node *) makeFuncCall(
+ SystemFuncName("multiset_intersect"),
+ list_make3($1, $5, makeBoolAConst($4, -1)), @2);
+ }
+ | a_expr MULTISET EXCEPT opt_all a_expr
+ {
+ $$ = (Node *) makeFuncCall(
+ SystemFuncName("multiset_except"),
+ list_make3($1, $5, makeBoolAConst($4, -1)), @2);
+ }
+ ;
+
+ opt_of: OF {}
+ /* FIXME: OF is an option in the SQL standard, but I cannot solve
+ shift/reduce errors without OF. To solve the errors, we might need
+ to make OF, MEMBER, and/or SUBMULTISET to reserved keywords. They
+ are reserved keywords in the SQL standard.
+ | {}
+ */
;
/*
*************** ColLabel: IDENT { $$ = $1; }
*** 11294,11300 ****
/* "Unreserved" keywords --- available for use as any kind of name.
*/
unreserved_keyword:
! ABORT_P
| ABSOLUTE_P
| ACCESS
| ACTION
--- 11359,11366 ----
/* "Unreserved" keywords --- available for use as any kind of name.
*/
unreserved_keyword:
! A
! | ABORT_P
| ABSOLUTE_P
| ACCESS
| ACTION
*************** unreserved_keyword:
*** 11421,11431 ****
--- 11487,11499 ----
| MAPPING
| MATCH
| MAXVALUE
+ | MEMBER
| MINUTE_P
| MINVALUE
| MODE
| MONTH_P
| MOVE
+ | MULTISET
| NAME_P
| NAMES
| NEXT
*************** unreserved_keyword:
*** 11513,11518 ****
--- 11581,11587 ----
| STORAGE
| STRICT_P
| STRIP_P
+ | SUBMULTISET
| SUPERUSER_P
| SYSID
| SYSTEM_P
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 499d357..d9f1f8a 100644
*** a/src/backend/utils/adt/array_userfuncs.c
--- b/src/backend/utils/adt/array_userfuncs.c
***************
*** 15,21 ****
--- 15,26 ----
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+ #include "utils/typcache.h"
+ static Datum array_cat_internal(PG_FUNCTION_ARGS, bool flatten);
+ static ArrayType *array_flatten(ArrayType *array);
+ static void check_concatinatable(Oid element_type1, Oid element_type2);
+ static void check_comparable(Oid element_type1, Oid element_type2);
/*-----------------------------------------------------------------------------
* array_push :
*************** array_push(PG_FUNCTION_ARGS)
*** 168,173 ****
--- 173,184 ----
Datum
array_cat(PG_FUNCTION_ARGS)
{
+ return array_cat_internal(fcinfo, false);
+ }
+
+ static Datum
+ array_cat_internal(PG_FUNCTION_ARGS, bool flatten)
+ {
ArrayType *v1,
*v2;
ArrayType *result;
*************** array_cat(PG_FUNCTION_ARGS)
*** 203,213 ****
--- 214,228 ----
if (PG_ARGISNULL(1))
PG_RETURN_NULL();
result = PG_GETARG_ARRAYTYPE_P(1);
+ if (flatten)
+ result = array_flatten(result);
PG_RETURN_ARRAYTYPE_P(result);
}
if (PG_ARGISNULL(1))
{
result = PG_GETARG_ARRAYTYPE_P(0);
+ if (flatten)
+ result = array_flatten(result);
PG_RETURN_ARRAYTYPE_P(result);
}
*************** array_cat(PG_FUNCTION_ARGS)
*** 218,231 ****
element_type2 = ARR_ELEMTYPE(v2);
/* Check we have matching element types */
! if (element_type1 != element_type2)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("cannot concatenate incompatible arrays"),
! errdetail("Arrays with element types %s and %s are not "
! "compatible for concatenation.",
! format_type_be(element_type1),
! format_type_be(element_type2))));
/* OK, use it */
element_type = element_type1;
--- 233,239 ----
element_type2 = ARR_ELEMTYPE(v2);
/* Check we have matching element types */
! check_concatinatable(element_type1, element_type2);
/* OK, use it */
element_type = element_type1;
*************** array_cat(PG_FUNCTION_ARGS)
*** 249,261 ****
* if both are empty, return the first one
*/
if (ndims1 == 0 && ndims2 > 0)
PG_RETURN_ARRAYTYPE_P(v2);
if (ndims2 == 0)
PG_RETURN_ARRAYTYPE_P(v1);
/* the rest fall under rule 3, 4, or 5 */
! if (ndims1 != ndims2 &&
ndims1 != ndims2 - 1 &&
ndims1 != ndims2 + 1)
ereport(ERROR,
--- 257,278 ----
* if both are empty, return the first one
*/
if (ndims1 == 0 && ndims2 > 0)
+ {
+ if (flatten)
+ v2 = array_flatten(v2);
PG_RETURN_ARRAYTYPE_P(v2);
+ }
if (ndims2 == 0)
+ {
+ if (flatten)
+ v1 = array_flatten(v1);
PG_RETURN_ARRAYTYPE_P(v1);
+ }
/* the rest fall under rule 3, 4, or 5 */
! if (!flatten &&
! ndims1 != ndims2 &&
ndims1 != ndims2 - 1 &&
ndims1 != ndims2 + 1)
ereport(ERROR,
*************** array_cat(PG_FUNCTION_ARGS)
*** 279,285 ****
ndatabytes1 = ARR_SIZE(v1) - ARR_DATA_OFFSET(v1);
ndatabytes2 = ARR_SIZE(v2) - ARR_DATA_OFFSET(v2);
! if (ndims1 == ndims2)
{
/*
* resulting array is made up of the elements (possibly arrays
--- 296,310 ----
ndatabytes1 = ARR_SIZE(v1) - ARR_DATA_OFFSET(v1);
ndatabytes2 = ARR_SIZE(v2) - ARR_DATA_OFFSET(v2);
! if (flatten)
! {
! ndims = 1;
! dims = (int *) palloc(sizeof(int));
! lbs = (int *) palloc(sizeof(int));
! dims[0] = nitems1 + nitems2;
! lbs[0] = 1;
! }
! else if (ndims1 == ndims2)
{
/*
* resulting array is made up of the elements (possibly arrays
*************** array_agg_finalfn(PG_FUNCTION_ARGS)
*** 544,546 ****
--- 569,1446 ----
PG_RETURN_DATUM(result);
}
+
+ /*
+ * array_cardinality :
+ * Return the number of elements in an array.
+ */
+ Datum
+ array_cardinality(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
+ int nitems;
+
+ nitems = ArrayGetNItems(ARR_NDIM(v), ARR_DIMS(v));
+
+ PG_RETURN_INT32(nitems);
+ }
+
+ /*
+ * trim_array :
+ * Remove elements at end of an array. Multi-dimensional array is
+ * flattened into one-dimensional array.
+ */
+ Datum
+ trim_array(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v;
+ int32 ntrimmed = PG_GETARG_INT32(1);
+ int arrtyplen;
+ Oid elmtype;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ int lower;
+ int upper;
+
+ if (ntrimmed < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("number of trimmed elements must not be negative")));
+
+ v = array_flatten(PG_GETARG_ARRAYTYPE_P(0));
+
+ arrtyplen = get_typlen(get_fn_expr_argtype(fcinfo->flinfo, 0));
+ lower = ARR_LBOUND(v)[0];
+ upper = ARR_DIMS(v)[0] - ntrimmed;
+ elmtype = ARR_ELEMTYPE(v);
+ get_typlenbyvalalign(elmtype, &elmlen, &elmbyval, &elmalign);
+
+ PG_RETURN_ARRAYTYPE_P(array_get_slice(
+ v, 1, &upper, &lower, arrtyplen, elmlen, elmbyval, elmalign));
+ }
+
+ /*
+ * Find TypeCacheEntry with comparison functions for element_type.
+ * We arrange to look up the compare functions only once per series of
+ * calls, assuming the element type doesn't change underneath us.
+ */
+ static TypeCacheEntry *
+ get_type_cache(Oid element_type, void **fn_extra)
+ {
+ TypeCacheEntry *type;
+
+ type = (TypeCacheEntry *) *fn_extra;
+ if (type == NULL ||
+ type->type_id != element_type)
+ {
+ type = lookup_type_cache(element_type,
+ TYPECACHE_EQ_OPR_FINFO | TYPECACHE_CMP_PROC_FINFO);
+ if (!OidIsValid(type->eq_opr_finfo.fn_oid) ||
+ !OidIsValid(type->cmp_proc_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify comparison functions for type %s",
+ format_type_be(element_type))));
+ *fn_extra = type;
+ }
+
+ return type;
+ }
+
+ static int
+ compare_element(const void *a, const void *b, void *arg)
+ {
+ FunctionCallInfo fn = (FunctionCallInfo) arg;
+
+ fn->arg[0] = *(const Datum *) a;
+ fn->arg[1] = *(const Datum *) b;
+ fn->argnull[0] = false;
+ fn->argnull[1] = false;
+ fn->isnull = false;
+ return DatumGetInt32(FunctionCallInvoke(fn));
+ }
+
+ /*
+ * Sort values and move nulls to the end.
+ * Returns number of non-null elements.
+ */
+ static int
+ sort_elements(TypeCacheEntry *type, Datum *values, bool *nulls, int nitems)
+ {
+ int nonnulls,
+ i;
+ FunctionCallInfoData fn;
+
+ if (nulls == NULL)
+ nonnulls = nitems;
+ else
+ {
+ /* move nulls to end of the array */
+ for (i = nonnulls = 0; i < nitems; i++)
+ {
+ if (!nulls[i])
+ {
+ values[nonnulls] = values[i];
+ nulls[nonnulls] = false;
+ nonnulls++;
+ }
+ }
+ for (i = nonnulls; i < nitems; i++)
+ nulls[i] = true;
+ }
+
+ /* sort non-null values */
+ InitFunctionCallInfoData(fn, &type->cmp_proc_finfo, 2, NULL, NULL);
+ qsort_arg(values, nonnulls, sizeof(Datum), compare_element, &fn);
+
+ return nonnulls;
+ }
+
+ /*
+ * Remove duplicated values in already sorted elements. Return the number
+ * of distinct elements. Note that there will be only one null value in
+ * the result even if there are some nulls.
+ */
+ static int
+ unique_elements(TypeCacheEntry *type, Datum *values, bool *nulls,
+ int nitems, int nonnulls)
+ {
+ int i,
+ n;
+ FunctionCallInfoData fn;
+
+ InitFunctionCallInfoData(fn, &type->eq_opr_finfo, 2, NULL, NULL);
+
+ for (i = n = 1; i < nonnulls; i++)
+ {
+ fn.arg[0] = values[i - 1];
+ fn.arg[1] = values[i];
+ fn.argnull[0] = false;
+ fn.argnull[1] = false;
+ fn.isnull = false;
+ if (!DatumGetBool(FunctionCallInvoke(&fn)))
+ values[n++] = values[i];
+ }
+ if (nonnulls < nitems)
+ nulls[n++] = true;
+
+ return n;
+ }
+
+ /*
+ * Deconstruct an array to a list of elements, sort them, and optinally
+ * remove duplicated values in them. Returns number of non-null elements.
+ */
+ static int
+ deconstruct_and_sort(ArrayType *array, bool unique,
+ Datum **values, bool **nulls, int *nitems,
+ void **fn_extra)
+ {
+ Oid element_type = ARR_ELEMTYPE(array);
+ int nonnulls;
+ TypeCacheEntry *type;
+
+ type = get_type_cache(element_type, fn_extra);
+ deconstruct_array(array,
+ element_type,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ values, nulls, nitems);
+
+ nonnulls = sort_elements(type, *values, nulls ? *nulls : NULL, *nitems);
+ if (unique)
+ *nitems = unique_elements(type, *values, nulls ? *nulls : NULL,
+ *nitems, nonnulls);
+
+ return nonnulls;
+ }
+
+ /*
+ * Sort an array, and optinally remove duplicated values in it.
+ */
+ static ArrayType *
+ sort_or_unique(ArrayType *array, bool unique, void **fn_extra)
+ {
+ Datum *values;
+ bool *nulls;
+ int nitems;
+ int lbs = 1;
+ Oid element_type = ARR_ELEMTYPE(array);
+ TypeCacheEntry *type;
+
+ deconstruct_and_sort(array, unique, &values, &nulls, &nitems, fn_extra);
+ type = (TypeCacheEntry *) *fn_extra;
+
+ return construct_md_array(values, nulls, 1, &nitems, &lbs, element_type,
+ type->typlen, type->typbyval, type->typalign);
+ }
+
+ /*
+ * array_sort :
+ * Sort an array in ascending order. Nulls are in the last.
+ */
+ Datum
+ array_sort(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_ARRAYTYPE_P(sort_or_unique(
+ PG_GETARG_ARRAYTYPE_P(0), false, &fcinfo->flinfo->fn_extra));
+ }
+
+ /*
+ * array_to_set :
+ * Remove duplicated elements in an array.
+ */
+ Datum
+ array_to_set(PG_FUNCTION_ARGS)
+ {
+ PG_RETURN_ARRAYTYPE_P(sort_or_unique(
+ PG_GETARG_ARRAYTYPE_P(0), true, &fcinfo->flinfo->fn_extra));
+ }
+
+ /*
+ * array_is_set :
+ * Return true iff an array has not duplicated values. Note that
+ * only one null is allowed in a set.
+ */
+ Datum
+ array_is_set(PG_FUNCTION_ARGS)
+ {
+ ArrayType *array = PG_GETARG_ARRAYTYPE_P(0);
+ bool result = true;
+ Datum *values;
+ bool *nulls;
+ int nitems;
+ int i;
+ TypeCacheEntry *type;
+ FunctionCallInfoData fn;
+
+ deconstruct_and_sort(array, false, &values, &nulls, &nitems,
+ &fcinfo->flinfo->fn_extra);
+ type = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+ InitFunctionCallInfoData(fn, &type->eq_opr_finfo, 2, NULL, NULL);
+
+ /* compare for each adjacent */
+ for (i = 1; i < nitems; i++)
+ {
+ /* some nulls at end of the array are allowed */
+ if (nulls[i])
+ {
+ result = (i == nitems - 1); /* only one null is allowd */
+ break;
+ }
+
+ fn.arg[0] = values[i - 1];
+ fn.arg[1] = values[i];
+ fn.argnull[0] = false;
+ fn.argnull[1] = false;
+ fn.isnull = false;
+ if (DatumGetBool(FunctionCallInvoke(&fn)))
+ {
+ result = false;
+ break;
+ }
+ }
+
+ PG_FREE_IF_COPY(array, 0);
+
+ PG_RETURN_BOOL(result);
+ }
+
+ /*
+ * submultiset_of : SUBMULTISET OF
+ * Return true iff v1 is a subset of v2,
+ */
+ Datum
+ submultiset_of(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v1 = NULL;
+ ArrayType *v2 = NULL;
+ bool result = false;
+ bool result_null = false;
+ Oid element_type;
+ Datum *values1,
+ *values2;
+ bool *nulls1,
+ *nulls2;
+ int nitems1,
+ nitems2,
+ nonnulls1,
+ nonnulls2,
+ n1,
+ n2,
+ unmatch;
+ TypeCacheEntry *type;
+ FunctionCallInfoData fn;
+
+ /* always null when v1 is null */
+ if (PG_ARGISNULL(0))
+ {
+ result_null = true;
+ goto ok;
+ }
+
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ nitems1 = ArrayGetNItems(ARR_NDIM(v1), ARR_DIMS(v1));
+
+ /* null when v2 is null, but false when v1 is empty */
+ if (PG_ARGISNULL(1))
+ {
+ if (nitems1 == 0)
+ result = true;
+ else
+ result_null = true;
+ goto ok;
+ }
+
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+ element_type = ARR_ELEMTYPE(v1);
+ check_comparable(element_type, ARR_ELEMTYPE(v2));
+
+ /* true when v1 is empty whether v2 is null or not */
+ if (nitems1 == 0)
+ {
+ result = true;
+ goto ok;
+ }
+
+ /* false when v1 has more elements than v2 */
+ nitems2 = ArrayGetNItems(ARR_NDIM(v2), ARR_DIMS(v2));
+ if (nitems1 > nitems2)
+ {
+ result = false;
+ goto ok;
+ }
+
+ /* compare non-null elements */
+ nonnulls1 = deconstruct_and_sort(v1, false,
+ &values1, &nulls1, &nitems1, &fcinfo->flinfo->fn_extra);
+ nonnulls2 = deconstruct_and_sort(v2, false,
+ &values2, &nulls2, &nitems2, &fcinfo->flinfo->fn_extra);
+ type = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+ InitFunctionCallInfoData(fn, &type->cmp_proc_finfo, 2, NULL, NULL);
+
+ unmatch = 0;
+ for (n1 = n2 = 0;
+ n1 < nonnulls1 && unmatch <= nitems2 - nonnulls2 && n2 < nonnulls2;)
+ {
+ int r;
+
+ fn.arg[0] = values1[n1];
+ fn.arg[1] = values2[n2];
+ fn.argnull[0] = false;
+ fn.argnull[1] = false;
+ fn.isnull = false;
+ r = DatumGetInt32(FunctionCallInvoke(&fn));
+
+ if (r < 0)
+ unmatch++;
+ if (r <= 0)
+ n1++;
+ if (r >= 0)
+ n2++;
+ }
+
+ unmatch += nitems1 - n1;
+ if (unmatch == 0)
+ result = true;
+ else if (unmatch > nitems2 - nonnulls2)
+ result = false;
+ else
+ result_null = true; /* v2 has equal or more nulls than unmatches */
+
+ ok:
+ if (v1 != NULL)
+ PG_FREE_IF_COPY(v1, 0);
+ if (v2 != NULL)
+ PG_FREE_IF_COPY(v2, 1);
+
+ if (result_null)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_BOOL(result);
+ }
+
+ /*
+ * multiset_union : MULTISET UNION [ DISTINCT | ALL ]
+ * Concatinate two arrays, and optionally remove duplicated values.
+ */
+ Datum
+ multiset_union(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v1,
+ *v2;
+ bool all = PG_GETARG_BOOL(2);
+ ArrayType *result;
+ Datum *values,
+ *values1,
+ *values2;
+ bool *nulls,
+ *nulls1,
+ *nulls2;
+ int nitems,
+ nitems1,
+ nitems2,
+ nonnulls;
+ Oid element_type1,
+ element_type2;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ if (PG_ARGISNULL(0) && PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ /* fast path for UNION ALL */
+ if (all)
+ return array_cat_internal(fcinfo, true);
+
+ /* Concatenating a null array is a no-op, just return the other input */
+ if (PG_ARGISNULL(0))
+ {
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+ result = sort_or_unique(v2, true, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(v2, 1);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+ if (PG_ARGISNULL(1))
+ {
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ result = sort_or_unique(v1, true, &fcinfo->flinfo->fn_extra);
+ PG_FREE_IF_COPY(v1, 0);
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+ element_type1 = ARR_ELEMTYPE(v1);
+ element_type2 = ARR_ELEMTYPE(v2);
+
+ check_concatinatable(element_type1, element_type2);
+ type = get_type_cache(element_type1, &fcinfo->flinfo->fn_extra);
+ deconstruct_array(v1,
+ element_type1,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ &values1, &nulls1, &nitems1);
+ deconstruct_array(v2,
+ element_type2,
+ type->typlen,
+ type->typbyval,
+ type->typalign,
+ &values2, &nulls2, &nitems2);
+
+ nitems = nitems1 + nitems2;
+ values = (Datum *) palloc(sizeof(Datum) * nitems);
+ nulls = (bool *) palloc(sizeof(bool) * nitems);
+
+ memcpy(values, values1, sizeof(Datum) * nitems1);
+ memcpy(values + nitems1, values2, sizeof(Datum) * nitems2);
+ memcpy(nulls, nulls1, sizeof(bool) * nitems1);
+ memcpy(nulls + nitems1, nulls2, sizeof(bool) * nitems2);
+
+ nonnulls = sort_elements(type, values, nulls, nitems);
+ nitems = unique_elements(type, values, nulls, nitems, nonnulls);
+ result = construct_md_array(values, nulls, 1, &nitems, &lbs,
+ element_type1,
+ type->typlen,
+ type->typbyval,
+ type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * Intersection of two sorted arrays. The first array is modified directly.
+ * Return length of the result.
+ */
+ static int
+ intersect_sorted_arrays(TypeCacheEntry *type,
+ Datum *values1, bool *nulls1, int nitems1,
+ const Datum *values2, const bool *nulls2, int nitems2)
+ {
+ int n1,
+ n2,
+ n;
+ FunctionCallInfoData fn;
+
+ InitFunctionCallInfoData(fn, &type->cmp_proc_finfo, 2, NULL, NULL);
+
+ /* add non-nulls */
+ for (n = n1 = n2 = 0;
+ n1 < nitems1 && !nulls1[n1] &&
+ n2 < nitems2 && !nulls2[n2];)
+ {
+ int r;
+
+ fn.arg[0] = values1[n1];
+ fn.arg[1] = values2[n2];
+ fn.argnull[0] = false;
+ fn.argnull[1] = false;
+ fn.isnull = false;
+ r = DatumGetInt32(FunctionCallInvoke(&fn));
+
+ if (r == 0)
+ values1[n++] = values1[n1];
+ if (r <= 0)
+ n1++;
+ if (r >= 0)
+ n2++;
+ }
+
+ /* skip non-nulls */
+ for (; n1 < nitems1 && !nulls1[n1]; n1++) {}
+ for (; n2 < nitems2 && !nulls2[n2]; n2++) {}
+
+ /* add nulls */
+ for (; n1 < nitems1 && n2 < nitems2; n1++, n2++, n++)
+ nulls1[n] = true;
+
+ return n;
+ }
+
+ /*
+ * multiset_intersect : MULTISET INTERSECT [ DISTINCT | ALL ]
+ * Intersection of two arrays, and optionally remove duplicated values.
+ */
+ Datum
+ multiset_intersect(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v1 = PG_GETARG_ARRAYTYPE_P(0);
+ ArrayType *v2 = PG_GETARG_ARRAYTYPE_P(1);
+ bool all = PG_GETARG_BOOL(2);
+
+ ArrayType *result;
+ Oid element_type = ARR_ELEMTYPE(v1);
+ Datum *values1,
+ *values2;
+ bool *nulls1,
+ *nulls2;
+ int nitems1,
+ nitems2;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ check_comparable(element_type, ARR_ELEMTYPE(v2));
+ deconstruct_and_sort(v1, !all, &values1, &nulls1, &nitems1,
+ &fcinfo->flinfo->fn_extra);
+ deconstruct_and_sort(v2, !all, &values2, &nulls2, &nitems2,
+ &fcinfo->flinfo->fn_extra);
+ type = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+
+ nitems1 = intersect_sorted_arrays(type,
+ values1, nulls1, nitems1,
+ values2, nulls2, nitems2);
+ result = construct_md_array(values1, nulls1, 1, &nitems1, &lbs,
+ element_type, type->typlen,
+ type->typbyval, type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * multiset_except : MULTISET EXCEPT [ DISTINCT | ALL ]
+ * Subtraction of two arrays, and optionally remove duplicated values.
+ */
+ Datum
+ multiset_except(PG_FUNCTION_ARGS)
+ {
+ ArrayType *v1;
+ ArrayType *v2;
+ bool all;
+ ArrayType *result;
+ Oid element_type;
+ Datum *values1,
+ *values2;
+ bool *nulls1,
+ *nulls2;
+ int nitems1,
+ nitems2;
+ int n1,
+ n2,
+ n;
+ int lbs = 1;
+ TypeCacheEntry *type;
+ FunctionCallInfoData fn;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ v1 = PG_GETARG_ARRAYTYPE_P(0);
+ all = PG_GETARG_BOOL(2);
+ element_type = ARR_ELEMTYPE(v1);
+
+ /* fast path for except null */
+ if (PG_ARGISNULL(1))
+ {
+ if (all)
+ PG_RETURN_ARRAYTYPE_P(array_flatten(v1));
+
+ deconstruct_and_sort(v1, false, &values1, &nulls1, &nitems1,
+ &fcinfo->flinfo->fn_extra);
+ type = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+
+ result = construct_md_array(values1, nulls1, 1, &nitems1, &lbs,
+ element_type, type->typlen,
+ type->typbyval, type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ v2 = PG_GETARG_ARRAYTYPE_P(1);
+
+ check_concatinatable(element_type, ARR_ELEMTYPE(v2));
+ deconstruct_and_sort(v1, !all, &values1, &nulls1, &nitems1,
+ &fcinfo->flinfo->fn_extra);
+ deconstruct_and_sort(v2, !all, &values2, &nulls2, &nitems2,
+ &fcinfo->flinfo->fn_extra);
+ type = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+ InitFunctionCallInfoData(fn, &type->cmp_proc_finfo, 2, NULL, NULL);
+
+ /* add non-nulls */
+ for (n = n1 = n2 = 0;
+ n1 < nitems1 && !nulls1[n1] &&
+ n2 < nitems2 && !nulls2[n2];)
+ {
+ int r;
+
+ fn.arg[0] = values1[n1];
+ fn.arg[1] = values2[n2];
+ fn.argnull[0] = false;
+ fn.argnull[1] = false;
+ fn.isnull = false;
+ r = DatumGetInt32(FunctionCallInvoke(&fn));
+
+ if (r < 0)
+ values1[n++] = values1[n1++];
+ else
+ n2++;
+ if (r == 0)
+ n1++;
+ }
+ for (; n1 < nitems1 && !nulls1[n1]; n1++, n++)
+ values1[n] = values1[n1];
+
+ /* add nulls */
+ if (n1 < nitems1 && nulls1[n1])
+ {
+ for (; n2 < nitems2 && !nulls2[n2]; n2++) {}
+ for (; n1 < nitems1 - (nitems2 - n2); n1++, n++)
+ nulls1[n] = true;
+ }
+
+ result = construct_md_array(values1, nulls1, 1, &n, &lbs, element_type,
+ type->typlen, type->typbyval, type->typalign);
+
+ PG_FREE_IF_COPY(v1, 0);
+ PG_FREE_IF_COPY(v2, 1);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * fusion aggregate function :
+ * Similar to array_agg, but the input values are arrays.
+ */
+ Datum
+ fusion_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ ArrayBuildState *state;
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "fusion_transfn called in non-aggregate context");
+ }
+
+ state = PG_ARGISNULL(0) ? NULL : (ArrayBuildState *) PG_GETARG_POINTER(0);
+ if (!PG_ARGISNULL(1))
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(1);
+ Oid elmtype;
+ int16 elmlen;
+ bool elmbyval;
+ char elmalign;
+ Datum *elems;
+ bool *nulls;
+ int nitems;
+ int i;
+
+ elmtype = ARR_ELEMTYPE(v);
+ get_typlenbyvalalign(elmtype, &elmlen, &elmbyval, &elmalign);
+ deconstruct_array(v, elmtype, elmlen, elmbyval, elmalign,
+ &elems, &nulls, &nitems);
+ for (i = 0; i < nitems; i++)
+ state = accumArrayResult(state, elems[i], nulls[i],
+ elmtype, aggcontext);
+
+ PG_FREE_IF_COPY(v, 1);
+ }
+
+ PG_RETURN_POINTER(state);
+ }
+
+ /*
+ * intersection aggregate function :
+ * Intersection of all input arrays.
+ */
+ typedef struct IntersectState
+ {
+ Oid element_type;
+ int nitems;
+ Datum *values;
+ bool *nulls;
+ } IntersectState;
+
+ Datum
+ intersection_transfn(PG_FUNCTION_ARGS)
+ {
+ MemoryContext aggcontext;
+ IntersectState *state;
+
+ if (!AggCheckCallContext(fcinfo, &aggcontext))
+ {
+ /* cannot be called directly because of internal-type argument */
+ elog(ERROR, "intersection_transfn called in non-aggregate context");
+ }
+
+ state = PG_ARGISNULL(0) ? NULL : (IntersectState *) PG_GETARG_POINTER(0);
+ if (!PG_ARGISNULL(1))
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(1);
+
+ if (state == NULL)
+ {
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(aggcontext);
+ state = (IntersectState *) palloc(sizeof(IntersectState));
+ state->element_type = ARR_ELEMTYPE(v);
+ deconstruct_and_sort(v, false,
+ &state->values, &state->nulls, &state->nitems,
+ &fcinfo->flinfo->fn_extra);
+ MemoryContextSwitchTo(oldcontext);
+ }
+ else
+ {
+ Datum *values;
+ bool *nulls;
+ int nitems;
+
+ check_concatinatable(state->element_type, ARR_ELEMTYPE(v));
+ deconstruct_and_sort(v, false, &values, &nulls, &nitems,
+ &fcinfo->flinfo->fn_extra);
+ state->nitems = intersect_sorted_arrays(
+ (TypeCacheEntry *) fcinfo->flinfo->fn_extra,
+ state->values, state->nulls, state->nitems,
+ values, nulls, nitems);
+ }
+
+ PG_FREE_IF_COPY(v, 1);
+ }
+
+ PG_RETURN_POINTER(state);
+ }
+
+ Datum
+ intersection_finalfn(PG_FUNCTION_ARGS)
+ {
+ IntersectState *state;
+ ArrayType *result;
+ int lbs = 1;
+ TypeCacheEntry *type;
+
+ state = PG_ARGISNULL(0) ? NULL : (IntersectState *) PG_GETARG_POINTER(0);
+ if (state == NULL)
+ PG_RETURN_NULL();
+
+ type = get_type_cache(state->element_type, &fcinfo->flinfo->fn_extra);
+ result = construct_md_array(state->values, state->nulls,
+ 1, &state->nitems, &lbs, state->element_type,
+ type->typlen, type->typbyval, type->typalign);
+
+ PG_RETURN_ARRAYTYPE_P(result);
+ }
+
+ /*
+ * Flatten multi-dimensional array into one-dimensional array.
+ * The lower bounds is adjusted to 1.
+ */
+ static ArrayType *
+ array_flatten(ArrayType *array)
+ {
+ ArrayType *result;
+ int ndims = ARR_NDIM(array);
+ int32 dataoffset;
+ int ndatabytes,
+ nbytes;
+ int nitems;
+
+ if (ndims < 1 || (ndims == 1 && ARR_LBOUND(array)[0] == 1))
+ return array;
+
+ nitems = ArrayGetNItems(ndims, ARR_DIMS(array));
+ ndatabytes = ARR_SIZE(array) - ARR_DATA_OFFSET(array);
+ if (ARR_HASNULL(array))
+ {
+ dataoffset = ARR_OVERHEAD_WITHNULLS(1, nitems);
+ nbytes = ndatabytes + dataoffset;
+ }
+ else
+ {
+ dataoffset = 0; /* marker for no null bitmap */
+ nbytes = ndatabytes + ARR_OVERHEAD_NONULLS(1);
+ }
+
+ result = (ArrayType *) palloc(nbytes);
+ SET_VARSIZE(result, nbytes);
+ result->ndim = 1;
+ result->dataoffset = dataoffset;
+ result->elemtype = ARR_ELEMTYPE(array);
+ ARR_DIMS(result)[0] = nitems;
+ ARR_LBOUND(result)[0] = 1;
+ /* data area is arg1 then arg2 */
+ memcpy(ARR_DATA_PTR(result), ARR_DATA_PTR(array), ndatabytes);
+ /* handle the null bitmap if needed */
+ if (ARR_HASNULL(result))
+ array_bitmap_copy(ARR_NULLBITMAP(result), 0,
+ ARR_NULLBITMAP(array), 0, nitems);
+
+ return result;
+ }
+
+ static void
+ check_concatinatable(Oid element_type1, Oid element_type2)
+ {
+ if (element_type1 != element_type2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot concatenate incompatible arrays"),
+ errdetail("Arrays with element types %s and %s are not "
+ "compatible for concatenation.",
+ format_type_be(element_type1),
+ format_type_be(element_type2))));
+ }
+
+ static void
+ check_comparable(Oid element_type1, Oid element_type2)
+ {
+ if (element_type1 != element_type2)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot compare incompatible arrays"),
+ errdetail("Arrays with element types %s and %s are not "
+ "compatible for comparison.",
+ format_type_be(element_type1),
+ format_type_be(element_type2))));
+ }
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 26966d2..2adedf3 100644
*** a/src/include/catalog/pg_aggregate.h
--- b/src/include/catalog/pg_aggregate.h
*************** DATA(insert ( 2901 xmlconcat2 - 0
*** 222,227 ****
--- 222,230 ----
/* array */
DATA(insert ( 2335 array_agg_transfn array_agg_finalfn 0 2281 _null_ ));
+ DATA(insert ( 3088 array_agg_transfn array_agg_finalfn 0 2281 _null_ ));
+ DATA(insert ( 3090 fusion_transfn array_agg_finalfn 0 2281 _null_ ));
+ DATA(insert ( 3093 intersection_transfn intersection_finalfn 0 2281 _null_ ));
/* text */
DATA(insert ( 3538 string_agg_transfn string_agg_finalfn 0 2281 _null_ ));
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index d6ed60a..acd6703 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2334 ( array_agg_fina
*** 1062,1067 ****
--- 1062,1097 ----
DESCR("array_agg final function");
DATA(insert OID = 2335 ( array_agg PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
DESCR("concatenate aggregate input into an array");
+ DATA(insert OID = 3079 ( cardinality PGNSP PGUID 12 1 0 0 f f f t f i 1 0 23 "2277" _null_ _null_ _null_ _null_ array_cardinality _null_ _null_ _null_ ));
+ DESCR("number of elements in array");
+ DATA(insert OID = 3080 ( trim_array PGNSP PGUID 12 1 0 0 f f f t f i 2 0 2277 "2277 23" _null_ _null_ _null_ _null_ trim_array _null_ _null_ _null_ ));
+ DESCR("remove elements end of array");
+ DATA(insert OID = 3081 ( array_sort PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ array_sort _null_ _null_ _null_ ));
+ DESCR("sort an array in ascending order");
+ DATA(insert OID = 3082 ( set PGNSP PGUID 12 1 0 0 f f f t f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ array_to_set _null_ _null_ _null_ ));
+ DESCR("remove duplicated values in an array");
+ DATA(insert OID = 3083 ( is_a_set PGNSP PGUID 12 1 0 0 f f f t f i 1 0 16 "2277" _null_ _null_ _null_ _null_ array_is_set _null_ _null_ _null_ ));
+ DESCR("no duplicated elements?");
+ DATA(insert OID = 3084 ( submultiset_of PGNSP PGUID 12 1 0 0 f f f f f i 2 0 16 "2277 2277" _null_ _null_ _null_ _null_ submultiset_of _null_ _null_ _null_ ));
+ DESCR("contained as subset?");
+ DATA(insert OID = 3085 ( multiset_union PGNSP PGUID 12 1 0 0 f f f f f i 3 0 2277 "2277 2277 16" _null_ _null_ _null_ _null_ multiset_union _null_ _null_ _null_ ));
+ DESCR("concatenate two arrays");
+ DATA(insert OID = 3086 ( multiset_intersect PGNSP PGUID 12 1 0 0 f f f t f i 3 0 2277 "2277 2277 16" _null_ _null_ _null_ _null_ multiset_intersect _null_ _null_ _null_ ));
+ DESCR("intersection of two arrays");
+ DATA(insert OID = 3087 ( multiset_except PGNSP PGUID 12 1 0 0 f f f f f i 3 0 2277 "2277 2277 16" _null_ _null_ _null_ _null_ multiset_except _null_ _null_ _null_ ));
+ DESCR("exception of two arrays");
+ DATA(insert OID = 3088 ( collect PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2283" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("concatenate aggregate elements into an array");
+ DATA(insert OID = 3089 ( fusion_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 2277" _null_ _null_ _null_ _null_ fusion_transfn _null_ _null_ _null_ ));
+ DESCR("fusion transition function");
+ DATA(insert OID = 3090 ( fusion PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("concatenate aggregate arrays into an array");
+ DATA(insert OID = 3091 ( intersection_transfn PGNSP PGUID 12 1 0 0 f f f f f i 2 0 2281 "2281 2277" _null_ _null_ _null_ _null_ intersection_transfn _null_ _null_ _null_ ));
+ DESCR("intersection transition function");
+ DATA(insert OID = 3092 ( intersection_finalfn PGNSP PGUID 12 1 0 0 f f f f f i 1 0 2277 "2281" _null_ _null_ _null_ _null_ intersection_finalfn _null_ _null_ _null_ ));
+ DESCR("intersection final function");
+ DATA(insert OID = 3093 ( intersection PGNSP PGUID 12 1 0 0 t f f f f i 1 0 2277 "2277" _null_ _null_ _null_ _null_ aggregate_dummy _null_ _null_ _null_ ));
+ DESCR("intersection of all inputs");
DATA(insert OID = 760 ( smgrin PGNSP PGUID 12 1 0 0 f f f t f s 1 0 210 "2275" _null_ _null_ _null_ _null_ smgrin _null_ _null_ _null_ ));
DESCR("I/O");
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 7c41312..b5cd584 100644
*** a/src/include/nodes/makefuncs.h
--- b/src/include/nodes/makefuncs.h
*************** extern TypeName *makeTypeNameFromOid(Oid
*** 71,76 ****
--- 71,77 ----
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype,
List *args, CoercionForm fformat);
+ extern FuncCall *makeFuncCall(List *funcname, List *args, int location);
extern DefElem *makeDefElem(char *name, Node *arg);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 578d3cd..5f59d22 100644
*** a/src/include/parser/kwlist.h
--- b/src/include/parser/kwlist.h
***************
*** 26,31 ****
--- 26,32 ----
*/
/* name, value, category */
+ PG_KEYWORD("a", A, UNRESERVED_KEYWORD)
PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("absolute", ABSOLUTE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("access", ACCESS, UNRESERVED_KEYWORD)
*************** PG_KEYWORD("login", LOGIN_P, UNRESERVED_
*** 232,242 ****
--- 233,245 ----
PG_KEYWORD("mapping", MAPPING, UNRESERVED_KEYWORD)
PG_KEYWORD("match", MATCH, UNRESERVED_KEYWORD)
PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("member", MEMBER, UNRESERVED_KEYWORD)
PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD)
PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD)
PG_KEYWORD("mode", MODE, UNRESERVED_KEYWORD)
PG_KEYWORD("month", MONTH_P, UNRESERVED_KEYWORD)
PG_KEYWORD("move", MOVE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("multiset", MULTISET, UNRESERVED_KEYWORD)
PG_KEYWORD("name", NAME_P, UNRESERVED_KEYWORD)
PG_KEYWORD("names", NAMES, UNRESERVED_KEYWORD)
PG_KEYWORD("national", NATIONAL, COL_NAME_KEYWORD)
*************** PG_KEYWORD("stdout", STDOUT, UNRESERVED_
*** 358,363 ****
--- 361,367 ----
PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD)
+ PG_KEYWORD("submultiset", SUBMULTISET, UNRESERVED_KEYWORD)
PG_KEYWORD("substring", SUBSTRING, COL_NAME_KEYWORD)
PG_KEYWORD("superuser", SUPERUSER_P, UNRESERVED_KEYWORD)
PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD)
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 78a4c8a..2e45286 100644
*** a/src/include/utils/array.h
--- b/src/include/utils/array.h
*************** extern ArrayType *create_singleton_array
*** 280,284 ****
--- 280,296 ----
extern Datum array_agg_transfn(PG_FUNCTION_ARGS);
extern Datum array_agg_finalfn(PG_FUNCTION_ARGS);
+ extern Datum array_cardinality(PG_FUNCTION_ARGS);
+ extern Datum trim_array(PG_FUNCTION_ARGS);
+ extern Datum array_sort(PG_FUNCTION_ARGS);
+ extern Datum array_to_set(PG_FUNCTION_ARGS);
+ extern Datum array_is_set(PG_FUNCTION_ARGS);
+ extern Datum submultiset_of(PG_FUNCTION_ARGS);
+ extern Datum multiset_union(PG_FUNCTION_ARGS);
+ extern Datum multiset_intersect(PG_FUNCTION_ARGS);
+ extern Datum multiset_except(PG_FUNCTION_ARGS);
+ extern Datum fusion_transfn(PG_FUNCTION_ARGS);
+ extern Datum intersection_transfn(PG_FUNCTION_ARGS);
+ extern Datum intersection_finalfn(PG_FUNCTION_ARGS);
#endif /* ARRAY_H */
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 4d86f45..e0b3ef5 100644
*** a/src/test/regress/expected/arrays.out
--- b/src/test/regress/expected/arrays.out
*************** select * from t1;
*** 1286,1288 ****
--- 1286,1487 ----
[5:5]={"(42,43)"}
(1 row)
+ -- MULTISET support
+ SELECT cardinality(ARRAY[1, 2, 3]), cardinality(ARRAY[[1, 2], [3, 4]]);
+ cardinality | cardinality
+ -------------+-------------
+ 3 | 4
+ (1 row)
+
+ SELECT trim_array(ARRAY[1, 2, 3], 2), trim_array(ARRAY[[1, 2], [3, 4]], 1);
+ trim_array | trim_array
+ ------------+------------
+ {1} | {1,2,3}
+ (1 row)
+
+ SELECT 'B' MEMBER OF ARRAY['A', 'B', 'C'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT 3 MEMBER OF ARRAY[1, 2], 3 MEMBER OF ARRAY[[1, 2], [3, 4]];
+ ?column? | ?column?
+ ----------+----------
+ f | t
+ (1 row)
+
+ SELECT 3 NOT MEMBER OF ARRAY[1, 2];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[3, 2, 1];
+ submultiset_of
+ ----------------
+ t
+ (1 row)
+
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[[1, 3], [2, 4]];
+ submultiset_of
+ ----------------
+ t
+ (1 row)
+
+ SELECT ARRAY[1, 1, 2] SUBMULTISET OF ARRAY[1, 2, 2];
+ submultiset_of
+ ----------------
+ f
+ (1 row)
+
+ SELECT ARRAY['A', 'B', 'C'] SUBMULTISET OF ARRAY['A', 'B', 'D'];
+ submultiset_of
+ ----------------
+ f
+ (1 row)
+
+ SELECT ARRAY['A', 'B', 'C'] NOT SUBMULTISET OF ARRAY['A', 'B', 'D'];
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ SELECT ARRAY[]::int[] SUBMULTISET OF NULL::int[];
+ submultiset_of
+ ----------------
+ t
+ (1 row)
+
+ SELECT NULL::int[] SUBMULTISET OF ARRAY[]::int[];
+ submultiset_of
+ ----------------
+
+ (1 row)
+
+ SELECT NULL::int[] SUBMULTISET OF NULL::int[];
+ submultiset_of
+ ----------------
+
+ (1 row)
+
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[1, NULL],
+ ARRAY[1, 2] SUBMULTISET OF ARRAY[3, NULL];
+ submultiset_of | submultiset_of
+ ----------------+----------------
+ | f
+ (1 row)
+
+ SELECT ARRAY[1, NULL] SUBMULTISET OF ARRAY[1, NULL];
+ submultiset_of
+ ----------------
+
+ (1 row)
+
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[NULL, 1, 2, 3];
+ submultiset_of
+ ----------------
+ t
+ (1 row)
+
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[1, 2, NULL];
+ submultiset_of
+ ----------------
+
+ (1 row)
+
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[NULL, 4, NULL];
+ submultiset_of
+ ----------------
+ f
+ (1 row)
+
+ SELECT ARRAY[1, 2, 3] IS A SET;
+ is_a_set
+ ----------
+ t
+ (1 row)
+
+ SELECT ARRAY['A', 'A', 'B'] IS A SET, ARRAY['A', 'A', 'B'] IS NOT A SET;
+ is_a_set | ?column?
+ ----------+----------
+ f | t
+ (1 row)
+
+ SELECT ARRAY[1, NULL] IS A SET, ARRAY[1, NULL, NULL] IS NOT A SET;
+ is_a_set | ?column?
+ ----------+----------
+ t | t
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET UNION ALL ARRAY[2, NULL];
+ multiset_union
+ --------------------------
+ {2,NULL,1,2,NULL,2,NULL}
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET UNION ARRAY[2, NULL];
+ multiset_union
+ ----------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET INTERSECT ALL ARRAY[2, NULL];
+ multiset_intersect
+ --------------------
+ {2,NULL}
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET INTERSECT ARRAY[2, NULL];
+ multiset_intersect
+ --------------------
+ {2,NULL}
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET EXCEPT ALL ARRAY[2, NULL];
+ multiset_except
+ -----------------
+ {1,2,NULL}
+ (1 row)
+
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET EXCEPT ARRAY[2, NULL];
+ multiset_except
+ -----------------
+ {1}
+ (1 row)
+
+ SELECT collect(s), fusion(a), intersection(a)
+ FROM (VALUES
+ ('A', ARRAY[1, 2, 3, 2, 2]),
+ ('B', ARRAY[1, 2, 4, 2]),
+ ('C', ARRAY[[3, 2], [2, 1]])
+ ) AS t(s, a);
+ collect | fusion | intersection
+ ---------+-----------------------------+--------------
+ {A,B,C} | {1,2,3,2,2,1,2,4,2,3,2,2,1} | {1,2,2}
+ (1 row)
+
+ SELECT array_sort(ARRAY[1, 3, 2, 3, NULL, 1, NULL]);
+ array_sort
+ -----------------------
+ {1,1,2,3,3,NULL,NULL}
+ (1 row)
+
+ SELECT array_sort(ARRAY[['A', 'D'], ['B', 'E'], ['C', 'F']]);
+ array_sort
+ ---------------
+ {A,B,C,D,E,F}
+ (1 row)
+
+ SELECT set(ARRAY[1, 3, 2, 3, NULL, 1, NULL]);
+ set
+ --------------
+ {1,2,3,NULL}
+ (1 row)
+
+ SELECT set(ARRAY[['A', 'C'], ['B', 'C'], ['C', 'A']]);
+ set
+ ---------
+ {A,B,C}
+ (1 row)
+
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index b0c096d..e21235c 100644
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
*************** insert into t1 (f1[5].q1) values(42);
*** 426,428 ****
--- 426,469 ----
select * from t1;
update t1 set f1[5].q2 = 43;
select * from t1;
+
+ -- MULTISET support
+
+ SELECT cardinality(ARRAY[1, 2, 3]), cardinality(ARRAY[[1, 2], [3, 4]]);
+ SELECT trim_array(ARRAY[1, 2, 3], 2), trim_array(ARRAY[[1, 2], [3, 4]], 1);
+ SELECT 'B' MEMBER OF ARRAY['A', 'B', 'C'];
+ SELECT 3 MEMBER OF ARRAY[1, 2], 3 MEMBER OF ARRAY[[1, 2], [3, 4]];
+ SELECT 3 NOT MEMBER OF ARRAY[1, 2];
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[3, 2, 1];
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[[1, 3], [2, 4]];
+ SELECT ARRAY[1, 1, 2] SUBMULTISET OF ARRAY[1, 2, 2];
+ SELECT ARRAY['A', 'B', 'C'] SUBMULTISET OF ARRAY['A', 'B', 'D'];
+ SELECT ARRAY['A', 'B', 'C'] NOT SUBMULTISET OF ARRAY['A', 'B', 'D'];
+ SELECT ARRAY[]::int[] SUBMULTISET OF NULL::int[];
+ SELECT NULL::int[] SUBMULTISET OF ARRAY[]::int[];
+ SELECT NULL::int[] SUBMULTISET OF NULL::int[];
+ SELECT ARRAY[1, 2] SUBMULTISET OF ARRAY[1, NULL],
+ ARRAY[1, 2] SUBMULTISET OF ARRAY[3, NULL];
+ SELECT ARRAY[1, NULL] SUBMULTISET OF ARRAY[1, NULL];
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[NULL, 1, 2, 3];
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[1, 2, NULL];
+ SELECT ARRAY[1, 2, 3] SUBMULTISET OF ARRAY[NULL, 4, NULL];
+ SELECT ARRAY[1, 2, 3] IS A SET;
+ SELECT ARRAY['A', 'A', 'B'] IS A SET, ARRAY['A', 'A', 'B'] IS NOT A SET;
+ SELECT ARRAY[1, NULL] IS A SET, ARRAY[1, NULL, NULL] IS NOT A SET;
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET UNION ALL ARRAY[2, NULL];
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET UNION ARRAY[2, NULL];
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET INTERSECT ALL ARRAY[2, NULL];
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET INTERSECT ARRAY[2, NULL];
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET EXCEPT ALL ARRAY[2, NULL];
+ SELECT ARRAY[2, NULL, 1, 2, NULL] MULTISET EXCEPT ARRAY[2, NULL];
+ SELECT collect(s), fusion(a), intersection(a)
+ FROM (VALUES
+ ('A', ARRAY[1, 2, 3, 2, 2]),
+ ('B', ARRAY[1, 2, 4, 2]),
+ ('C', ARRAY[[3, 2], [2, 1]])
+ ) AS t(s, a);
+ SELECT array_sort(ARRAY[1, 3, 2, 3, NULL, 1, NULL]);
+ SELECT array_sort(ARRAY[['A', 'D'], ['B', 'E'], ['C', 'F']]);
+ SELECT set(ARRAY[1, 3, 2, 3, NULL, 1, NULL]);
+ SELECT set(ARRAY[['A', 'C'], ['B', 'C'], ['C', 'A']]);