Re: onlyvalue aggregate (was: First Aggregate Funtion?)

2015-10-28 18:03 GMT+01:00 Marko Tiikkaja <marko(at)joh(dot)to>:

> On 10/28/15 5:53 PM, Pavel Stehule wrote:
>
>> what is use case for this function and why it should be in core?
>>
>
> Corey had one example in his email, but I can offer another one which came
> up this week at $work. The query looked something like this:
>
> SELECT a, sum(amount), onlyvalue(rolling_count)
> FROM
> (
> SELECT a, amount, count(*) OVER (ORDER BY a) AS rolling_count
> FROM tbl
> ) ss
> GROUP BY a;
>
> We know that all the values for the column are going to be the same value
> for every "a", so we could use min() or max(). But the advantage of
> "onlyvalue" is that it actually checks that, so if someone went and changed
> the window frame to do something slightly different, the query would blow
> up instead of silently returning the (now likely incorrect) minimum or
> maximum value. It's also self-documenting for the reader of such queries.
>
> In my experience this problem comes up often enough that it would be make
> sense to have this aggregate in core.
>

This function is pretty inconsistent with any other builtin aggregate
function, so it is one argument against to push it to core. Next, this
function can be pretty simply implemented in PLpgSQL. And my last argument
- I don't remember too often request for this functionality. It looks like
module for PGXN much more, than PG core functionality.

Regards

Pavel

>
>
> .m
>