Re: MULTISET and additional functions for ARRAY

Hello

2010/11/15 Itagaki Takahiro <itagaki.takahiro@gmail.com>:
> On Fri, Nov 12, 2010 at 00:02, Itagaki Takahiro
> <itagaki.takahiro@gmail.com> wrote:
>> Postgres supports ARRAY data types well, but there are some
>> more array functions in the SQL standard. Also, the standard
>> has MULTISET data type, that is an unordered array.
>
> Here is a WIP patch for multiset function supports. Note that multiset
> types are not added in the patch; it just adds functions and syntax.
> Arguments or result types of those functions are anyarray rather than
> anymultiset. The result type is always flatten into on-dimensional
> array because some functions requires per-element operations; I'm not
> sure how we should treat trim_array( 2D 3x3 array, 2 elements ). So,
> it is treated as trim_array( 9 elements array, 2 elements ) in the patch.
>
> The SQL standard defines special syntax for multiset. I added four
> unreserved keywords for them; A, MEMBER, MULTISET, and SUB.
> (I don't like such ad-hoc syntax, but it is the standard...)
> Some of the new expressions are just syntactic sugar for existing
> other expressions or new functions. For example, "$1 MEMBER OF $2" is
> expanded to "$1 = ANY ($2)" and "$1 IS A SET" to "multiset_is_a_set($1)".
>
> I have not researched the spec yet enough, especially NULLs in collections.
> I'll continue to check the details.
>
> BTW, some of the intermediate products to implement those features might
> be useful if exported. like array_sort() and array_unique(). If there is
> demand, I'll add those functions, too.
>
> Any comments for the approach or detailed features?
>

I has not a standard, so I can't to speak about conformance with
standard, but I must to say, so these functionality should be
extremely useful for plpgsql programming. You can see samples a some
functions on the net for array sorting, for reduce a redundant values
via SQL language. I am sure, so implementation in C will be much
faster. Maybe can be useful to implement a searching on sorted array.
You can hold a flag if multiset is sorted or not.

Regards

Pavel Stehule

> === New features ===
> - [FUNCTION] cardinality(anyarray) => integer
> - [FUNCTION] trim_array(anyarray, nTrimmed integer) => anyarray
> - [FUNCTION] element(anyarray) => anyelement
> - [SYNTAX] $1 MEMBER OF $2 --> $1 = ANY ($2)
> - [SYNTAX] $1 SUB MULTISET OF $2 --> $1 <@ $2
> - [SYNTAX] $1 IS A SET  --> multiset_is_a_set($1)
> - [SYNTAX] $1 MULTISET UNION [ALL | DISTINCT] $2 -->
> multiset_union($1, $2, all?)
> - [SYNTAX] $1 MULTISET INTERSECT [ALL | DISTINCT] $2 -->
> multiset_intersect($1, $2, all?)
> - [SYNTAX] $1 MULTISET EXCEPT [ALL | DISTINCT] $2 -->
> multiset_except($1, $2, all?)
> - [AGGREGATE] collect(anyelement) => anyarray --> same as array_agg()
> - [AGGREGATE] fusion(anyarray) => anyarray
> - [AGGREGATE] intersection(anyarray) => anyarray
>
> --
> Itagaki Takahiro
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>