Thanks all. I will try some of these suggestions.
On Sun, May 11, 2008 at 3:58 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Mag Gam wrote: > > > I am trying to find the difference between the size column. So the > > desired output would be > > > > ts | size| Diff > > -------------------+-----+------ > > 2002-03-16 | 11 | 0 > > > > 2002-03-17 | 15 | 4 > > 2002-03-18 | 18 | 3 > > 2002-03-19 | 12 | -6 > > > > > > I need the first column to be 0, since it will be 11-11. The second > > colum is 15-11. The third column is 18-15. The fourth column is 12-18. > > > > Any thoughts about this? > > Here's one way to do this with PL/PgSQL. It's probably not the most > efficient, but it does work. For this code to be safe `size' must never > be NULL and `ts' must be unique across all records in the input set. > > CREATE OR REPLACE FUNCTION x_diff( > OUT ts TIMESTAMP, > OUT size INTEGER, > OUT diff INTEGER) > RETURNS SETOF record AS $$ > DECLARE > cur_x x; > last_size INTEGER := null; > BEGIN > FOR cur_x IN SELECT * FROM x ORDER BY ts ASC LOOP > ts := cur_x.ts; > size := cur_x.size; > IF last_size IS NULL THEN > -- First record in set has diff `0' because the differences > -- are defined against the previous, rather than next, > -- record. > diff := 0; > ELSE > diff := cur_x.size - last_size; > END IF; > last_size := cur_x.size; > RETURN NEXT; > END LOOP; > RETURN; > END; > $$ LANGUAGE 'plpgsql' STRICT; > > If you need to constrain the range of values processed that's not too > tricky - either feed the function a refcursor for a query result set to > iterate over, or pass it parameters to constrain the query with a WHERE > clause. The former is more flexible, the latter is easier to use. > > -- > Craig Ringer >
