I see 3 solutions.
A) self join
B) define a procedure that return a set of records.
this use only a single table scan on the ordered table
not tested, just the genera idea:
CREATE OR REPLACE FUNCTION foo()
RETURNS SETOF <yourRecordType> AS
...
DELARE
previous_time int8; --(or whaever datatype you have)
rec record ;
rec2 <yourRecordType>;
BEGIN
FOR rec in select id, time from yourtable ORDER BY ID LOOP
select into rec2 id, rec.time - previous_time;
return next rec2;
END LOOP;
END;
...
C) embedding R in Postgres
http://www.joeconway.com/oscon-pres-2003-1.pdf
http://www.omegahat.org/RSPostgres/
This may be a solution to implement complex cross-rows aggregation.
But I never got the time to test it;
I'd be interested to know which concerns this solution can show
(stability, memory & cpu load, concurent requests....)
Cheers,
Marc
