multiset-doc-20110112.patch
application/octet-stream
Filename: multiset-doc-20110112.patch
Type: application/octet-stream
Part: 0
Message:
Re: multiset patch review
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/array.sgml | 0 | 0 |
| doc/src/sgml/func.sgml | 0 | 0 |
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index bb4657e..35de925 100644
*** a/doc/src/sgml/array.sgml
--- b/doc/src/sgml/array.sgml
*************** INSERT ... VALUES (E'{"\\\\","\\""}');
*** 706,709 ****
--- 706,817 ----
</tip>
</sect2>
+ <sect2 id="multisets">
+ <title>Multiset Support</title>
+
+ <indexterm>
+ <primary>array</primary>
+ <secondary>multiset</secondary>
+ </indexterm>
+
+ <para>
+ Multiset is another collection data type specified in the SQL standard.
+ It is similar to arrays, but the order of elements is irrelevant.
+ <productname>PostgreSQL</productname> doesn't support distinct multiset
+ data type, but has serveral functions and operators based on array types.
+ </para>
+
+ <para>
+ <literal>MEMBER OF</> and <literal>SUBMULTISET OF</> operators returns
+ true when the element or subset is contained by the collection.
+ <literal>MEMBER OF</> is exactly same as <literal>= ANY</> operator.
+ On the other hand, <literal>SUBMULTISET OF</> differs from <literal><@</>
+ because it returns true only if the container have equal or more elements
+ the containded collection.
+ <programlisting>
+ SELECT 2 MEMBER OF ARRAY[1,2], 2 = ANY(ARRAY[1,2]);
+ ?column? | ?column?
+ ----------+----------
+ t | t
+ (1 row)
+
+ SELECT ARRAY[1,1] SUBMULTISET OF ARRAY[1,2],
+ ARRAY[1,1] <@ ARRAY[1,2];
+ submultiset_of | ?column?
+ ----------------+----------
+ f | t
+ (1 row)
+ </programlisting>
+ </para>
+
+ <para>
+ <literal>IS A SET</> operator returns true when the collection has
+ no duplicated values. A collection that has two or more NULLs are not
+ considered as a set.
+ <programlisting>
+ SELECT ARRAY[1,2,3] IS A SET,
+ ARRAY[1,1,2] IS A SET,
+ ARRAY[1,NULL,NULL] IS A SET;
+
+ is_a_set | is_a_set | is_a_set
+ ----------+----------+----------
+ t | f | f
+ (1 row)
+ </programlisting>
+ <function>set</function> function returns a collection of unique elements
+ as like as <literal>DISTINCT</> clause in a query.
+ <programlisting>
+ SELECT set(ARRAY[1,2,NULL,2,NULL,1,2]);
+ set
+ ------------
+ {1,2,NULL}
+ (1 row)
+ </programlisting>
+ </para>
+
+ <para>
+ <literal>MULTISET EXCEPT</>, <literal>MULTISET INTERSECT</>, and
+ <literal>MULTISET UNION</> operator combine two collections as like as
+ set operations in a query (see <xref linkend="queries-union">).
+ They can have optional <literal>ALL</> or <literal>DISTINCT</> options.
+ If <literal>DISTINCT</> is specified or not specified, they eliminates
+ duplicated elements before the set operations.
+ <programlisting>
+ SELECT ARRAY[2,NULL,1,2,NULL] MULTISET UNION ARRAY[2,NULL],
+ ARRAY[2,NULL,1,2,NULL] MULTISET INTERSECT ARRAY[2,NULL],
+ ARRAY[2,NULL,1,2,NULL] MULTISET EXCEPT ARRAY[2,NULL];
+ multiset_union | multiset_intersect | multiset_except
+ ----------------+--------------------+-----------------
+ {1,2,NULL} | {2,NULL} | {1}
+ (1 row)
+
+ SELECT ARRAY[2,NULL,1,2,NULL] MULTISET UNION ALL ARRAY[2,NULL],
+ ARRAY[2,NULL,1,2,NULL] MULTISET INTERSECT ALL ARRAY[2,NULL],
+ ARRAY[2,NULL,1,2,NULL] MULTISET EXCEPT ALL ARRAY[2,NULL];
+ multiset_union | multiset_intersect | multiset_except
+ --------------------------+--------------------+-----------------
+ {2,NULL,1,2,NULL,2,NULL} | {2,NULL} | {1,2,NULL}
+ (1 row)
+ </programlisting>
+ </para>
+
+ <note>
+ <para>
+ Since multisets are actually arrays, some of operators and functions still
+ treats them as arrays. The following example shows two collections are
+ sub-multiset of each other, but not equal with <literal>=</> operator
+ because they are arrays in fact; they have the same set of elements, but
+ differ in the order of elements.
+ <programlisting>
+ SELECT a SUBMULTISET OF b, b SUBMULTISET OF a, a = b
+ FROM (VALUES(ARRAY[1,2], ARRAY[2,1])) t(a, b);
+ submultiset_of | submultiset_of | ?column?
+ ----------------+----------------+----------
+ t | t | f
+ (1 row)
+ </programlisting>
+ </para>
+ </note>
+
+ </sect2>
</sect1>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 04769f1..aae831c 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT NULLIF(value, '(none)') ...
*** 10196,10201 ****
--- 10196,10311 ----
</para>
<para>
+ <xref linkend="multiset-operators-table"> shows the multiset operators
+ available for array types. See <xref linkend="multisets"> for more details
+ and limitations.
+ </para>
+
+ <table id="multiset-operators-table">
+ <title>Multiset Operators</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Operator</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <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 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>
+
+ <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>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <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>
<xref linkend="array-functions-table"> shows the functions
available for use with array types. See <xref linkend="arrays">
for more information and examples of the use of these functions.
*************** SELECT NULLIF(value, '(none)') ...
*** 10226,10240 ****
--- 10336,10362 ----
<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 ****
--- 10466,10482 ----
<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 ****
--- 10512,10550 ----
<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)') ...
*** 10421,10428 ****
</note>
<para>
See also <xref linkend="functions-aggregate"> about the aggregate
! function <function>array_agg</function> for use with arrays.
</para>
</sect1>
--- 10587,10601 ----
</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)') ...
*** 10468,10474 ****
<function>array_agg(<replaceable class="parameter">expression</replaceable>)</function>
</entry>
<entry>
! any
</entry>
<entry>
array of the argument type
--- 10641,10647 ----
<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)') ...
*** 10568,10573 ****
--- 10741,10762 ----
<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)') ...
*** 10606,10611 ****
--- 10795,10832 ----
<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>