Re: Need to subtract values between previous and current row

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