Re: arrays and functions in plpgsql

Todd Kover <kovert@omniscient.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