Re: Difference in columns

------=_Part_9894_16391028.1210553908162
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks all. I will try some of these suggestions.


On Sun, May 11, 2008 at 3:58 PM, Craig Ringer
<craig@[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
>

------=_Part_9894_16391028.1210553908162
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_9894_16391028.1210553908162--