Thread: How to find current row number relative to window frame

How to find current row number relative to window frame

From
Art Ruszkowski
Date:
Hello,

I have a user defined aggregate function and in Sfunc I need to
reference current row number relative to window frame. Ideallly I would like to have following construct:

select my_aggregate(x,current_row_number_relative_to window) over
(order by y rows between n preceding and current row) as..... from ….


Any ideas how I can do this.  Thanks Art.


Re: How to find current row number relative to window frame

From
Tom Lane
Date:
Art Ruszkowski <a_@prudenttradingsystems.com> writes:
> I have a user defined aggregate function and in Sfunc I need to
> reference current row number relative to window frame. Ideallly I would like to have following construct:

> select my_aggregate(x,current_row_number_relative_to window) over
> (order by y rows between n preceding and current row) as..... from ….

Couldn't you make the aggregate track the current row number as part of
its state?

While it's a bit annoying to have to do that, I don't see any other
solution that doesn't involve converting the aggregate into a
full-fledged window function, which you probably don't want to do.
(Even then, nodeWindowAgg.c doesn't seem to expose a function for
row number within frame, only row number within partition.)

            regards, tom lane