arrays and functions in plpgsql - Mailing list pgsql-novice
From | Todd Kover |
---|---|
Subject | arrays and functions in plpgsql |
Date | |
Msg-id | 200409172306.i8HN6v8t019739@guinness.omniscient.com Whole thread Raw |
Responses |
Re: arrays and functions in plpgsql
|
List | pgsql-novice |
I'm trying to write my own aggregate function. I'm getting messed up in both the sfunc and finalfunc and I'm reasonably certain it's because I'm not understanding how to pass arrays in properly. I didn't piece it together from the docs and I didn't find examples of how to do this. I'm running postgresql 7.4.3. 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'; as an eventual final function. When I call it by hand via: select float8_jitter_sum('{5,6,.3}'); select float8_jitter_sum('{5,9}'); I get back nothing where I think I should get back division of the first two elements of the array. What am I missing? If you're particularly curious, I've attached the entirity of what my aggregate code looks like with an example table and select. (I'm basically adding up the variance between an ordered list of elements, skipping things where the previous element is NULL). Since I havne't gotten past the above, I haven't really looked closely into what else I'm doing wrong (I'm certain I am because the final select gives me an array value error), but if you spot something obvious, that'd be appreciated to. thanks in advance, -Todd ----<snip>---- create or replace function float8_jitter_add(float8[], interval) returns float8[3] as ' declare v_old_state ALIAS FOR $1; v_rtt ALIAS FOR $2; v_state[3] float8; BEGIN IF v_state is NULL THEN v_state[0] := 0; v_state[1] := 0; v_state[2] := NULL; ELSIF v_rtt IS NOT NULL THEN if v_state[2] IS NOT NULL THEN v_state[0] := v_old_state[0] + (v_old_state[2] - v_rtt); v_state[1] := v_old_state[1] + 1; END IF; v_state[2] := v_tt; ELSE v_state[2] := NULL; END IF; return v_state; END; ' language 'plpgsql'; 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'; drop aggregate jitter( interval ); create aggregate jitter ( basetype = interval, sfunc = float8_jitter_add, stype = float8[], finalfunc = float8_jitter_sum, initcond = '(NULL,NULL)' ); create table test ( thing integer, start timestamp, finish timestamp ); insert into test values (1, current_timestamp, current_timestamp + '1 sec'); insert into test values (1, current_timestamp, current_timestamp + '2 sec'); select thing, jitter(finish-start) from test group by thing;
pgsql-novice by date: