PostgreSQL function to iterate through/act on many rows with state
- by Claudiu
I have a database with columns looking like:
session | order | atype | amt
--------+-------+-------+-----
1 | 0 | ADD | 10
1 | 1 | ADD | 20
1 | 2 | SET | 35
1 | 3 | ADD | 10
2 | 0 | SET | 30
2 | 1 | ADD | 20
2 | 2 | SET | 55
It represents actions happening. Each session starts at 0. ADD adds an amount, while SET sets it. I want a function to return the end value of a session, e.g.
SELECT session_val(1); --returns 45
SELECT session_val(2); --returns 55
Is it possible to write such a function/query? I don't know how to do any iteration-like things with SQL, or if it's possible at all.
