arrays and functions in plpgsql

From: Todd Kover <kovert(at)omniscient(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: arrays and functions in plpgsql
Date: 2004-09-17 23:06:57
Message-ID: 200409172306.i8HN6v8t019739@guinness.omniscient.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2004-09-17 23:16:14 Re: arrays and functions in plpgsql
Previous Message Kumar S 2004-09-17 18:59:00 ERROR: COPY <Table Name> FROM <file>