On 2023-06-23 Fr 04:23, Joel Jacobson wrote:
On Fri, Jun 23, 2023, at 08:40, jian he wrote:
I played around array_func.c
many of the code can be used for multiset data type.
now I imagine multiset as something like one dimension array. (nested
is somehow beyond the imagination...).
Are you suggesting it might be a better idea to start over completely
and work on a new code base that is based on arrayfuncs.c,
and aim for MULTISET/SET or anyhashset from start, that would not
only support int4/int8/uuid but any type?
Before we run too far down this rabbit hole, let's discuss the storage
implications of using multisets. ISTM that for small base datums like
integers it will be a substantial increase in size, since you'll need an
addition int for the item count, unless some very clever tricks are played.
As for this older discussion referred to upthread, if the SQL Standards
Committee hasn't acted on it by now it seem reasonable to think they are
unlikely to.
Just for reference, Here's some description of Oracle's suport for
Multisets from
<https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Oracle-Support-for-Optional-Features-of-SQLFoundation2011.html#GUID-3BA98AEC-FAAD-4F21-A6AD-F696B5D36D56>:
Multisets in the standard are supported as nested table types in
Oracle. The Oracle nested table data type based on a scalar type ST is
equivalent, in standard terminology, to a multiset of rows having a
single field of type ST and named column_value. The Oracle nested
table type based on an object type is equivalent to a multiset of
structured type in the standard.
Oracle supports the following elements of this feature on nested
tables using the same syntax as the standard has for multisets:
The CARDINALITY function
The SET function
The MEMBER predicate
The IS A SET predicate
The COLLECT aggregate
All other aspects of this feature are supported with non-standard
syntax, as follows:
To create an empty multiset, denoted MULTISET[] in the standard,
use an empty constructor of the nested table type.
To obtain the sole element of a multiset with one element, denoted
ELEMENT (<multiset value expression>) in the standard, use a scalar
subquery to select the single element from the nested table.
To construct a multiset by enumeration, use the constructor of the
nested table type.
To construct a multiset by query, use CAST with a multiset
argument, casting to the nested table type.
To unnest a multiset, use the TABLE operator in the FROM clause.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
