PostgreSQL function to iterate through/act on many rows with state

Question

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.

Answer 1

To use my query, you will need to begin each transaction with a SET 0 record. Also, you will not be able to add another atype, only SET and ADD will work as I suggest.

This query is untested.

The idea is to simply sum the latest (most ultimate) SET and every ADD which follows it:

SELECT SUM(amt) 
    WHERE session=X 
      AND order >= ( SELECT MAX(order) 
                         WHERE atype='SET' 
                           AND session=X );

Answer 2

Well, this is hardly pretty but it's functional:

select sum(amt) as session_val
from (
  select segment,
         max(segment) over() as max_segment,
         amt
  from (
    select sum(case when atype = 'SET' then 1 else 0 end)
               over(order by "order") as segment,
           amt
    from command
    where session = 2
  ) x
) x
where segment = max_segment

It is quite simple in PL/pgsql though:

create function session_val(session int) returns int stable strict
language plpgsql as $$
declare
  value int := 0;
  rec command%rowtype;
begin
  for rec in select * from command where command.session = session_val.session loop
    if rec.atype = 'SET' then
      value := rec.amt;
    elsif rec.atype = 'ADD' then
      value := value + rec.amt;
    end if;
  end loop;
  return value;
end $$;

So take your pick, I guess.