drop type if exists flip_stype cascade;
create type flip_stype as
(
last_val numeric,
next_val numeric
);
drop function if exists flip_state (flip_stype, numeric) cascade;
create or replace function flip_state (flip_stype, numeric)
returns flip_stype
language sql
immutable
parallel safe
as
$$
select row
(
case when $1.last_val <= $2 then $1.next_val else $1.last_val end,
case when $1.last_val <= $2 then $2 else $1.last_val end
)
$$;
drop function if exists flip_final (flip_stype) cascade;
create or replace function flip_final (flip_stype)
returns numeric
language plpgsql
immutable
parallel safe
as
$$
begin
raise notice '%', $1;
return $1.next_val;
end;
$$;
drop aggregate if exists flip_flop (numeric) cascade;
create or replace aggregate flip_flop (numeric)
(
sfunc = flip_state,
stype = flip_stype,
finalfunc = flip_final,
initcond = '(0,0)'
);
with t as
(
select 1 as rn, 0 as val, 0 as target union all
select 2, 1 , 1 union all
select 3, 0 , 1 union all
select 4, 0 , 1 union all
select 5, 1 , 1 union all
select 6,-1 , 0 union all
select 7, 1 , 1 union all
select 8, 0 , 1 union all
select 9,-1 , 0 union all
select 10,-1, 0 union all
select 11,null, 0 union all
select 12,1,1 union all
select 6.1,0 , 0 union all
select 6.2,0 , 0
)
select
rn,
val,
target,
flip_flop(val) over(order by rn)
from t;
Telegram: DB developers channel