Prev: [HACKERS] Update on true serializable techniques in MVCC
Next: An example of bugs for Hot Standby
From: David Fetter on 15 Dec 2009 17:22
On Tue, Dec 15, 2009 at 10:28:49PM +0100, Pavel Stehule wrote:
> Hello
>
> I am looking on new feature - ORDER clause in aggregate, and I thing,
> so we are able to effectively implement some non standard, but well
> known aggregates.
>
> a) function median - it is relative frequent request - with usually
> slow implementation

Makes a lot of sense. I suspect we'll have to provide several
different medians, as there are several precise, useful, and
conflicting definitions. Some examples below:

http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

> b) function listagg (it is analogy of group_concat from MySQL) - it
> should simplify report generating and some other

This is redundant, as it's equivalent to array_to_string(array_agg()).

If it's done as syntactic sugar over that, it's fine. If it's a
separate implementation, it's a bad idea. Probably best as an
optional module of some kind.

Cheers,
David.
--
David Fetter <david(a)fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter(a)gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Pavel Stehule on 16 Dec 2009 01:36
2009/12/15 David Fetter <david(a)fetter.org>:
> On Tue, Dec 15, 2009 at 10:28:49PM +0100, Pavel Stehule wrote:
>> Hello
>>
>> I am looking on new feature - ORDER clause in aggregate, and I thing,
>> so we are able to effectively implement some non standard, but well
>> known aggregates.
>>
>> a) function median - it is relative frequent request - with usually
>> slow implementation
>
> Makes a lot of sense.  I suspect we'll have to provide several
> different medians, as there are several precise, useful, and
> conflicting definitions.  Some examples below:
>
> http://www.simple-talk.com/sql/t-sql-programming/median-workbench/

In this article the are two medians - statistical and financial. I am
for both. But only one can be named "median".

>
>> b) function listagg (it is analogy of group_concat from MySQL) - it
>> should simplify report generating and some other
>
> This is redundant, as it's equivalent to array_to_string(array_agg()).

when I implement it in orafce - the almost all code was parameters
checking. Implementation is trivial, because important part of work
was done with array_agg support. It is wrapper over stringInfo.

>
> If it's done as syntactic sugar over that, it's fine.  If it's a
> separate implementation, it's a bad idea.  Probably best as an
> optional module of some kind

I am not sure if implementation as syntactic sugar is best. It needs
parser changes. And final implementation will not be effective.
listagg should be faster - string operations are significantly
simpler than array op. What more - PostgreSQL cannot well wrap SRF and
aggregates. The implementation could be analogy with functions
generate_series and generate_subscripts. generate_subscripts is
redundant, but well implementation needs C coding. Implementation as
syntactic sugar is possible - it could be nice too - listagg is more
shorter than array_to_string(array_agg()) - but it has overhead with
array.

There could be some steps. First - implementation as wrapper over
arrays, second - if this functionality will be popular - native
implementation in C?

Regards
Pavel

>
> Cheers,
> David.
> --
> David Fetter <david(a)fetter.org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fetter(a)gmail.com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 16 Dec 2009 09:45
On Wed, Dec 16, 2009 at 1:36 AM, Pavel Stehule <pavel.stehule(a)gmail.com> wrote:
>>> b) function listagg (it is analogy of group_concat from MySQL) - it
>>> should simplify report generating and some other
>>
>> This is redundant, as it's equivalent to array_to_string(array_agg()).
>
> when I implement it in orafce - the almost all code was parameters
> checking. Implementation is trivial, because important part of work
> was done with array_agg support. It is wrapper over stringInfo.
>
>>
>> If it's done as syntactic sugar over that, it's fine.  If it's a
>> separate implementation, it's a bad idea.  Probably best as an
>> optional module of some kind
>
> I am not sure if implementation as syntactic sugar is best. It needs
> parser changes.

I don't think this is important enough to change the parser for it. I
don't see a concrete proposal for syntax here, but it seems like the
standard function call syntax should be adequate.

....Robert

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 16 Dec 2009 10:17
Pavel Stehule <pavel.stehule(a)gmail.com> wrote:
> 2009/12/15 David Fetter <david(a)fetter.org>:

>> http://www.simple-talk.com/sql/t-sql-programming/median-workbench/
>
> In this article the are two medians - statistical and financial. I
> am for both. But only one can be named "median".

Well, since the statistical median requires that you specify whether
you want the left or right median (in case there are an even number
of values), you could either have two median functions, one of which
would take an extra parameter for this, or you could have median,
left_median, and right_median functions.

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Thom Brown on 16 Dec 2009 10:24
2009/12/15 Pavel Stehule <pavel.stehule(a)gmail.com>

> Hello
>
> I am looking on new feature - ORDER clause in aggregate, and I thing,
> so we are able to effectively implement some non standard, but well
> known aggregates.
>
> a) function median - it is relative frequent request - with usually
> slow implementation
>
> b) function listagg (it is analogy of group_concat from MySQL) - it
> should simplify report generating and some other
>
> What is your opinion? Do you like to see these functions in core?
>
>
>
I'm probably missing the point here, but when I originally saw MySQL's
group_concat function, I found it odd that it featured ordering
functionality. Shouldn't the order by determined by the query itself?
Otherwise it's almost as if its separating the relationship between the
result column and the resultset.

Thom
 |  Next  |  Last
Pages: 1 2
Prev: [HACKERS] Update on true serializable techniques in MVCC
Next: An example of bugs for Hot Standby