BEGIN WORK;

CREATE TYPE float_state AS (precision_lost bool, value float);


CREATE OR REPLACE FUNCTION float_sum(state float_state, value float)
  RETURNS float_state AS
$$
DECLARE newstate float_state;
BEGIN
	IF state IS NULL THEN
		IF value IS NULL THEN
			RETURN NULL;
		ELSE
			newstate.value := value;
			newstate.precision_lost := false;
			return newstate;
		END IF;
	END IF;

	IF state.value + value = state.value AND value <> 0 THEN
		newstate.precision_lost := true;
		newstate.value := state.value;
	ELSE
		newstate.precision_lost := false;
		newstate.value := state.value + value;
	END IF;
	
	RETURN newstate;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION float_sum_inv(state float_state, value float)
  RETURNS float_state AS
$$
DECLARE newstate float_state;
BEGIN
	IF state.precision_lost = true THEN
		RETURN NULL;
	ELSE
		newstate.value := state.value - value;
		RETURN newstate;
	END IF;
END;
$$
LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OR REPLACE FUNCTION float_sum_final(state float_state)
  RETURNS float AS
$$
BEGIN
	IF NOT(state IS NULL) THEN
		RETURN state.value;
	ELSE
		RETURN NULL;
	END IF;
END;
$$
LANGUAGE plpgsql IMMUTABLE;


CREATE AGGREGATE mysum (float) (
    stype = float_state,
    sfunc = float_sum,
    invfunc = float_sum_inv,
    finalfunc = float_sum_final
  );

select mysum(v)  from (values(1,1e20),(2,1)) b(i,v);

-- forces re-aggregate due to precision loss
--explain (analyze, verbose)
select mysum(v) over (order by i rows between current row and unbounded following) from (values(1,1e20),(2,1)) b(i,v);

-- does not force reaggregate.
--explain (analyze, verbose)
select mysum(v) over (order by i rows between current row and unbounded following) from (values(1,1),(2,2),(3,3)) b(i,v);

rollback;