Re: arrays and functions in plpgsql

Todd Kover <kovert(at)omniscient(dot)com> writes:
> I have:

> create or replace function float8_jitter_sum(float8[])
> returns float8 as '
> declare
> v_state ALIAS FOR $1;
> v_avg float8;
> BEGIN
> v_avg := NULL;
> if v_state[1] != 0 THEN
> v_avg := v_state[0] / v_state[1];
> END IF;
> return v_avg;
> END;
> ' language 'plpgsql';

Array indexes start from one by default, so I think you need

if v_state[2] != 0 THEN
v_avg := v_state[1] / v_state[2];

> create aggregate jitter (
> basetype = interval,
> sfunc = float8_jitter_add,
> stype = float8[],
> finalfunc = float8_jitter_sum,
> initcond = '(NULL,NULL)'
> );

This initcond will not work either, since that's not valid syntax for an
array (and we don't yet support nulls as array elements anyway).
But you are already testing for v_state IS NULL, so just leave out the
initcond and let it default to a NULL array.

> IF v_state is NULL THEN
> v_state[0] := 0;
> v_state[1] := 0;
> v_state[2] := NULL;

This is going to be a problem too. You could write

v_state := ''{0,0,0}'';

but initializing the array one-element-at-a-time won't work in 7.4.
(I think it will work in 8.0, FWIW.) You'll have to invent some
convention other than NULL for the third entry, also. Maybe use
a 4-element array and let the 4th element be 1 or 0 according to
whether the 3rd element is really meaningful?

regards, tom lane