drop function if exists flip_state (boolean, boolean, boolean) cascade;
create or replace function flip_state (boolean, boolean, boolean)
returns boolean
language sql
immutable
parallel safe
as
$$
select
case
when $3 is true then false
else
case
when $2 is true then true
else $1
end
end
$$;
drop aggregate if exists flip_flop_custom (boolean, boolean) cascade;
create or replace aggregate flip_flop_cust (boolean, boolean)
(
sfunc = flip_state,
stype = boolean,
initcond = 'false'
);
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_cust(val = 1, coalesce(val, -1) = -1) over(order by rn)
from t;
Telegram: DB developers channel