Re: TODO items for window functions

2008/12/29 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> The core window-functions patch is now committed and ready for wider
> testing. However, there are a number of unfinished items, at least
> some of which I'd like to see addressed before 8.4 release. In rough
> order of importance:
>
> * Support creation of user-defined window functions. I think this is
> a "must have" for 8.4 --- we are not in the habit of building
> nonextensible basic features. It doesn't seem that hard either.
> I think all we need do is to allow "WINDOW" as an attribute keyword
> in CREATE FUNCTION. Does anyone have an objection or a better idea?

The reason I and people decided window functions are not able to be
defined by user is whether Window functions API is ready for exposure.
If we agree with the current design is not changing for the long
future, I don't have any objection.
My only concern is for plpgsql. For c functions we can define user
functions but what about other pls?

> * Implement support for non-default window framing clauses. Most likely
> it's too late to consider getting the whole feature done for 8.4, but
> I wonder whether we could support the restricted case of allowing just
> these two combinations
> BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (which is default)
> BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
> I said earlier that we didn't really need to address this for 8.4,
> but my thinking was flawed. The case I think is really important
> is to allow last_value() to do something useful, and you can hardly
> argue that it's useful without an ORDER BY to define which row in the
> partition is "last".

Frame clause concern is shrinking situation. For shrinking frame, we
must do something to optimize aggregate not to recalculate from the
first of the frame. So as far as we stay in UNBOUNDED PRECEDING, it is
not so hard work, inclulding BETWEEN UNBOUNDED PRECEDING AND n
FOLLOWING.

And surveying sgml docs, I found this is not correct.

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/select.sgml?r1=1.112&r2=1.113

+ default framing behavior, which is equivalent to the framing clause
+ <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</>.

the default frame with ORDER BY clause is RANGE BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW, as aggregates perform rows peer to the
current row.

Regards,

--
Hitoshi Harada