From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: MULTISET and additional functions for ARRAY |
Date: | 2010-11-15 05:37:44 |
Message-ID: | AANLkTim8QnUqDc3VNb7_kFBgfOB2azB9TYe61enpBUhb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello
2010/11/15 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
> On Fri, Nov 12, 2010 at 00:02, Itagaki Takahiro
> <itagaki(dot)takahiro(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-11-15 05:41:40 | Re: changing MyDatabaseId |
Previous Message | Tom Lane | 2010-11-15 05:26:48 | Re: Refactoring the Type System |