How to reference data from next row?

Question

I'm writing a function in PostgreSQL 9.2.

For a table of stock prices and dates, I want to calculate the percentage 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.

I'm aware that the WITH statement is probably not supposed to be above the IF statement. This is just how I've thought about it logically so far:

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 <= $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  <---How do I reference r + 1??/
   ELSE
        RETURN NIL
   ENDIF
END
$func$;

How to reference data from the next row properly?

Answer 1

I want to calculate the percentage change from the previous day for each entry

Study the basics first. Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

Major points why the example is nonsense

• You cannot hand in an identifier like you do. Identifiers cannot be parameterized in plain SQL. You'd need dynamic SQL for that.
  Of course, not actually needed for your objective. There is only one table involved. Would be nonsense to parameterize it.

• Don't use type names as identifiers. I use _date instead of date as parameter name and renamed your table column to asset_date. ALTER your table definition accordingly.

• A function fetching data from a table is never IMMUTABLE. Don't declare it as such. Read the manual.

• You are mixing SQL syntax with PL/pgSQL elements in nonsensical ways. WITH is part of an SELECT statement and cannot be mixed with PL/pgSQL control structures like LOOP or IF.

Sanitized function

A proper function could look like this (one of many ways):

CREATE FUNCTION percentage_change_func(_asset_symbol text)
  RETURNS TABLE(asset_date date, price numeric, pct_change numeric)
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   last_price numeric;
BEGIN
   FOR asset_date, price IN
      SELECT a.asset_date, a.price  -- table-qualify to disambiguate!
      FROM   asset_histories a
      WHERE  a.asset_symbol = _asset_symbol 
      ORDER  BY a.asset_date  -- traverse ascending
   LOOP
      pct_change := price / last_price;  -- NULL if last_price is NULL
      RETURN NEXT;
      last_price := price;
   END LOOP;
END
$func$;

Performance shouldn't be so bad, but still pointless complication.

Proper solution: plain query

The simplest (and probably fastest) way would be with the window function lag():

SELECT asset_date, price
     , price / lag(price) OVER (ORDER BY asset_date) AS pct_change
FROM   asset_histories
WHERE  asset_symbol = _asset_symbol 
ORDER  BY asset_date;

Standard deviation

As per your later comment, you want to calculate statistical numbers like standard deviation.
Postgres has dedicated aggregate functions for statistics.

Answer 2

Simple things like just calculating the per_change, can be done within a view instead, this would also result in faster results

create view view_stock_details AS ( SELECT 
    date, 
    price, 
    symbol, 
    pervious_day_close, 
    (price-pervious_day_close)/pervious_day_close*100 as per_change 
FROM (
    SELECT
        date,
        price,
        symbol,
        ( SELECT price FROM asset_histories t WHERE t.symbol = outers.symbol AND t.date < outers.date limit 1 ) as pervious_day_close
    FROM 
        asset_histories as outers
    ); 

To view the stock details, you can then use

SELECT 
    *
FROM
    view_stock_details
WHERE
    date = '2012-01-03' 
    AND symbol = 'A'