Re: Difference in columns

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/>