Postgres PL/pgSQL recursive calculation

Question

Maybe some one could point me to the right direction. I have faced the problem writing PL/pgSQL sentence where I need to calculate "calculation" column which depends on previous month value.

Originally I have columns B and C, and need to calculate "Calculation"

The formula in excel for 4 row would look like this: =C4/(B4+OFFSET(D4;-1;0))

Row  month          B       C       Calculation
3    2012.02.01     1       15      13,20
4    2012.03.01     6       26      1,32
5    2012.04.01     8       21      2,29
6    2012.05.01     10      31      2,54
7    2012.06.01     11      10      0,72

Maybe someone have any ideas how to reach this. I know LAG and LEAD functions, but those ones can only reference 'real' columns not the calculation itself.

p.s this is sample data and formula, the real one is much more complex.

I would be thankful for any questions/ideas

Answer 1

Well I think you could use RECURSIVE CTE:

with recursive CTE_R as 
(
    select T.Row, T.month, T.B, T.C, 13.2 as Calculation
    from temp as T
    where T.Row = 3

    union all

    select T.Row, T.month, T.B, T.C, T.C / (T.B + C.Calculation) as Calculation
    from CTE_R as C
        inner join temp as T on T.Row = C.Row + 1
)
select *
from CTE_R

Another way to do it is to create your own custom aggregate SQL FIDDLE EXAMPLE:

create function aggr_test_func(decimal(29, 10), int, int)
returns decimal(29, 10)
language SQL as
$func$
    select $3 / ($2     + $1)
$func$;

create aggregate aggr_test (int, int)
(
    sfunc = aggr_test_func,
    stype = decimal(29, 10),
    initcond = 0
);

select *, aggr_test(B, C) over (order by row asc) as Calculation
from test;