I have an SQL table called "credits" which contain the following columns:

• id (Serial ID for rows (1,2,3,....))
• account_id (ID of associated client)
• change (int4)
• rolling_change

Every time during/after a row insert, I'd like the result of this Query to be the "rolling_change" column's value:

SELECT SUM(change)
FROM credits
WHERE account_id = {account_id} AND id < {this_id};

How can I make this process happen automatically on every row insert?

(I'm using DBeaver for reference)

CodePudding user response：

Here's an example that updates the empty rolling_change after inserts.

CREATE TABLE credits (
    id          SERIAL PRIMARY KEY,
    account_id  INT NOT NULL,
    change      INT NOT NULL, 
    rolling_change INT
);

CREATE OR REPLACE FUNCTION calc_credits_rolling_change() 
RETURNS trigger AS $calc_rolling_change$
BEGIN
    UPDATE credits tgt
    SET rolling_change = src.rolling_change
    FROM (
        SELECT id
        , SUM(change) OVER (PARTITION BY account_id 
                            ORDER BY id) AS rolling_change
        FROM credits
    ) src
    WHERE src.id = tgt.id
      AND tgt.rolling_change IS NULL;

    RETURN NEW;
END;
$calc_rolling_change$ LANGUAGE plpgsql;

CREATE TRIGGER trg_credits_rolling_change
  AFTER INSERT
  ON credits
  EXECUTE PROCEDURE calc_credits_rolling_change();

INSERT INTO credits (account_id, change) VALUES
  (1, 1), (1, 0)
, (2, 1), (2, 1), (2, 0);

INSERT INTO credits (account_id, change) VALUES
  (3, 2), (3, 1), (1, 10)

select * from credits order by account_id, id;

Demo on db<>fiddle here