Thanks all. I will try some of these suggestions. <br /><br /><br /><div class="gmail_quote">On Sun, May 11, 2008 at
3:58PM, Craig Ringer <<a href="mailto:craig@postnewspapers.com.au">craig@postnewspapers.com.au</a>> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><div class="Ih2E3d">Mag Gam wrote:<br /><br /> > I am trying to find the difference between the
sizecolumn. So the<br /> > desired output would be<br /> ><br /> > ts | size| Diff<br /> >
-------------------+-----+------<br/> > 2002-03-16 | 11 | 0<br /> ><br /> > 2002-03-17 | 15
|4<br /> > 2002-03-18 | 18 | 3<br /> > 2002-03-19 | 12 | -6<br /> ><br /> ><br /> > I
needthe first column to be 0, since it will be 11-11. The second<br /> > colum is 15-11. The third column is 18-15.
Thefourth column is 12-18.<br /> ><br /> > Any thoughts about this?<br /><br /></div>Here's one way to do this
withPL/PgSQL. It's probably not the most<br /> efficient, but it does work. For this code to be safe `size' must
never<br/> be NULL and `ts' must be unique across all records in the input set.<br /><br /> CREATE OR REPLACE FUNCTION
x_diff(<br/> OUT ts TIMESTAMP,<br /> OUT size INTEGER,<br /> OUT diff INTEGER)<br /> RETURNS SETOF record AS
$$<br/> DECLARE<br /> cur_x x;<br /> last_size INTEGER := null;<br /> BEGIN<br /> FOR cur_x IN SELECT * FROM x
ORDERBY ts ASC LOOP<br /> ts := cur_x.ts;<br /> size := cur_x.size;<br /> IF last_size IS NULL
THEN<br/> -- First record in set has diff `0' because the differences<br /> -- are defined
againstthe previous, rather than next,<br /> -- record.<br /> diff := 0;<br /> ELSE<br />
diff := cur_x.size - last_size;<br /> END IF;<br /> last_size := cur_x.size;<br />
RETURNNEXT;<br /> END LOOP;<br /> RETURN;<br /> END;<br /> $$ LANGUAGE 'plpgsql' STRICT;<br /><br /> If you need
toconstrain the range of values processed that's not too<br /> tricky - either feed the function a refcursor for a
queryresult set to<br /> iterate over, or pass it parameters to constrain the query with a WHERE<br /> clause. The
formeris more flexible, the latter is easier to use.<br /><br /> --<br /><font color="#888888">Craig Ringer<br
/></font></blockquote></div><br/>
