> select row1, sum (row1 until current row) from table;
> the second column is like sum() of all the rows until/include this row.
"Until this row" is not very specific. I'll assume that you are assuming
a time ordering for the rows, so that you are really saying that you
want the aggregate of something up to (and including?) the current
something.
Here is a little example of how you might do that:
lockhart=# create table t1 (i int, b timestamp, e timestamp);
CREATE
lockhart=# insert into t1 values (1, 'now', timestamp 'now' + '1 sec');
(repeat three times, slowly...)
lockhart=# create function xsum(timestamp)
lockhart-# returns int as 'select cast(sum(i) as int)
lockhart-# from t1 where b <= $1;' language 'sql';
CREATE
lockhart=# select *, xsum(b) from t1;
i | b | e | xsum
---+------------------------+------------------------+------
1 | 2002-02-05 17:14:37+00 | 2002-02-05 17:14:38+00 | 1
1 | 2002-02-05 17:14:40+00 | 2002-02-05 17:14:41+00 | 2
1 | 2002-02-05 17:14:41+00 | 2002-02-05 17:14:42+00 | 3
1 | 2002-02-05 17:14:42+00 | 2002-02-05 17:14:43+00 | 4
(4 rows)
Or if you want to sum a difference of times, try
lockhart=# create function tsum(timestamp)
lockhart-# returns interval as 'select sum(e-b)
lockhart-# from t1 where b <= $1;' language 'sql';
CREATE
lockhart=# select *, tsum(b) from t1;
i | b | e | tsum
---+------------------------+------------------------+----------
1 | 2002-02-05 17:14:37+00 | 2002-02-05 17:14:38+00 | 00:00:01
1 | 2002-02-05 17:14:40+00 | 2002-02-05 17:14:41+00 | 00:00:02
1 | 2002-02-05 17:14:41+00 | 2002-02-05 17:14:42+00 | 00:00:03
1 | 2002-02-05 17:14:42+00 | 2002-02-05 17:14:43+00 | 00:00:04
(4 rows)
This is an expensive query! I'll bet you can recast your specification
to something simpler which doesn't require executing a subquery for
every row.
hth
- Thomas
