Re: Need to subtract values between previous and current row

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

 

 

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