2015-10-28 18:03 GMT+01:00 Marko Tiikkaja <marko@joh.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.