title: Iterating through PostgreSQL records. How to reference data from next row?
body: <p>I'm new to PostgreSQL and writing functions here is tough as nails. So I'm hoping someone can help let me know how to do what I'm trying to do.</p>
  
  <p>I have a table of stock prices and dates. I want to calculate the percent change from the previous day for each entry. For the earliest day of data, there won't be a previous day, so that entry can simply be Nil. Can someone look over my function and help me with<br>
  a) how to reference data from the next row and<br>
  b) help me clean it up? </p>
  
  <p>I'm aware that the <code>WITH</code> statement is probably not supposed to be above the <code>IF</code> statement. However logically, this is how I've thought about it so far and thus how I've written it. If you could advise how that is supposed to look it would be much appreciated as well.</p>
  
  <pre><code>CREATE FUNCTION percentage_change_func(asset_histories) RETURNS 
  numeric LANGUAGE sql IMMUTABLE AS $func$
  
  DECLARE
  r asset_histories%rowtype
  BEGIN   
  WITH twodaysdata AS (SELECT date,price,asset_symbol FROM asset_histories 
             WHERE asset_symbol = $1.asset_symbol 
             AND asset_histories.date &lt;= $1.date 
             ORDER BY date DESC LIMIT 2), 
           numberofrecords AS (SELECT count(*) FROM twodaysdata) 
  
  IF numberofrecords = 2 THEN
          RETURN r.price / (r+1).price - 1  &lt;---How do I reference r + 1??/
  ELSE
          RETURN NIL
  ENDIF
  END
  $func$
  </code></pre>
  
  <p>PostgreSQL 9.2.</p>