Iterating through PostgreSQL records. How to refer

问题:

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.

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
a) how to reference data from the next row and
b) help me clean it up?

I'm aware that the WITH statement is probably not supposed to be above the IF 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.

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$

PostgreSQL 9.2.

回答1:

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

Generally you need to study the basics, before you start asking questions.
Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

Major points why the example is nonsense

• First, 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, you don't actually need that, according to your requirements. There is only one table involved. It is nonsense to try and 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 can never be IMMUTABLE. Read the manual.

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

Proper 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) AS
$func$
DECLARE
   last_price numeric;
BEGIN

FOR asset_date, price IN
   SELECT a.asset_date, a.price
   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$ LANGUAGE plpgsql STABLE

Performance shouldn't be so bad, but it's just 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.
There are dedicated aggregate functions for statistics in PostgreSQL.

回答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'