PostgreSQL - How to get the previous(lag) calculated value

Question

I would like to get the previous(lag) calculated value?

   id  | value
-------|-------
    1  |   1
    2  |   3
    3  |   5
    4  |   7
    5  |   9

What I am trying to achieve is this:

   id  | value | new value
-------|-------|-----------
   1   |   1   |   10      <-- 1 * lag(new_value)
   2   |   3   |   30      <-- 3 * lag(new_value)
   3   |   5   |  150      <-- 5 * lag(new_value)
   4   |   7   | 1050      <-- 7 * lag(new_value)
   5   |   9   | 9450      <-- 9 * lag(new_value)

What I have tried:

SELECT value,
       COALESCE(lag(new_value) OVER () * value, 10) AS new_value
FROM table

Error:

ERROR: column "new_value" does not exist

Answer 1

Similar to Juan's answer but I thought I'd post it anyway. It at least avoids the need for the ID column and doesn't have the empty row at the end:

with recursive all_data as (
  select value, value * 10 as new_value
  from data
  where value = 1

  union all

  select c.value, 
         c.value * p.new_value
  from data c
    join all_data p on p.value < c.value
  where c.value = (select min(d.value) 
                   from data d 
                   where d.value > p.value)
)
select *
from all_data
order by value;

The idea is to join exactly one row in the recursive part to exactly one "parent" row. While the "exactly one parent" can be done with a derived table and a lateral join (which surprisingly does allow the limit). The "exactly one row" from the "child" in the recursive part can unfortunately only be done using the sub-select with a min().

The where c.value= (...) wouldn't be necessary if it was possible to use an order by and limit in the recursive part as well, but unfortunately that is not supported in the current Postgres version.

Online example: http://rextester.com/WFBVM53545

Answer 2

My bad, this isnt that easy as I thought. Got a very close result but still need some tunning.

DEMO

WITH RECURSIVE t(n, v) AS (
    SELECT MIN(value), 10 
    FROM Table1

    UNION ALL
    SELECT (SELECT min(value) from Table1 WHERE value > n), 
           (SELECT min(value) from Table1 WHERE value > n) * v
    FROM t
    JOIN Table1 on t.n = Table1.value    
)    
SELECT n, v 
FROM t;