Stored generated column without limitation-CodePudding

Let's say I have a table containing four columns in Postgres -- ID (int), Year (int), Product (str), and Revenue (int). I would like to store a calculated column to the table that is based on retrieving the previous year's data:

It seems the stored generated columns have a lot of limitations, notably:

The generation expression can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

What would be the preferred way to do the above? It seems like the only way to do this without creating a materialized view (which would needlessly copy all the data, which I don't want), is to do an alter table and add a new column. For example:

create table sales (id int, year int, product varchar(40), revenue int);
insert into sales VALUES (1,1996, 'car', 12);
insert into sales VALUES (2,1996, 'phone', 7);
insert into sales VALUES (3,1997, 'car', 13);
insert into sales VALUES (4,1997, 'phone', 11);
------------------------------------------------------------
alter table sales add last_revenue INT 
    DEFAULT LAG(Revenue) OVER (PARTITION by Product ORDER BY year);
    -- window function will be rejected in alter table, 
    -- but using this to show what I want to achieve

CodePudding user response：

use view. If you want materialize then use materialzied view

CREATE VIEW include_previous_year AS (
    SELECT
        *,
        LAG(Revenue) OVER (PARTITION BY Product ORDER BY year)
    FROM sales)

CodePudding user response：

You could update the existing data with the following UPDATE:

START TRANSACTION;
ALTER TABLE sales ADD revenue_last INT DEFAULT NULL;
UPDATE sales JOIN (
    SELECT
      id,
      LAG(revenue) OVER (PARTITION BY product ORDER BY year) revenue_last 
    FROM sales) sales_calc USING (id)
SET sales.revenue_last=sales_calc.revenue_last;
COMMIT;

If you want to also update and data coming in, you can add in a trigger.