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;


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Todd Kover <kovert (AT) omniscient (DOT) com> writes:
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];

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.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thanks. All your suggestions helped a bunch and make sense. Although
I'm running into an issue:

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, 0, 0}'';
ELSIF v_rtt IS NOT NULL THEN
if v_state[4] = 1 THEN
v_state[1] := v_old_state[2] + (v_old_state[3] - v_rtt);
v_state[2] := v_old_state[2] + 1;
END IF;
v_state[3] := v_rtt;
v_state[4] := 1;
ELSE
v_state[4] := 0;
END IF;
return v_state;
END;
' language 'plpgsql';


testdb=# select float8_jitter_add('{.1,.2,.3,1}', 5);
ERROR: "$1" is declared CONSTANT
CONTEXT: compile of PL/pgSQL function "float8_jitter_add" near line 12

Near as I can tell, I'm not reassigning $1 (or v_old_state) and line 12
is the END IF, which looks to be to be ok as do the lines around it.

I'm probably missing something obvious..

thanks again,
-Todd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Todd Kover <kovert (AT) omniscient (DOT) com> writes:
Hmm. I'm not sure what the plpgsql parser will make of that. I think
you probably wanted
v_state float8[3];

(note that you really want [4], not that it actually matters since PG
doesn't enforce the array size; float8[] would do as well)

Also, I think you need to change v_state to v_old_state in several more
places than you did, or else assign v_old_state to v_state up front.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

oops. That was me randomly trying things I thought I understood. (I
thought I got all those :-)

indeed I did. Still have the same problem, though:

create or replace function float8_jitter_add(float8[], interval)
returns float8[] as '
declare
v_old_state ALIAS FOR $1;
v_rtt ALIAS FOR $2;
v_state float8[];
BEGIN
IF v_old_state is NULL THEN
v_state = ''{0, 0, 0, 0}'';
ELSIF v_rtt IS NOT NULL THEN
if v_old_state[4] = 1 THEN
v_state[1] := v_old_state[2] + (v_old_state[3] - v_rtt);
v_state[2] := v_old_state[2] + 1;
END IF;
v_state[3] := v_rtt;
v_state[4] := 1;
ELSE
v_state[4] := 0;
END IF;
return v_state;
END;
' language 'plpgsql';

testdb=# select float8_jitter_add('{.1,.2,.3,1}', 5);
ERROR: "$1" is declared CONSTANT
CONTEXT: compile of PL/pgSQL function "float8_jitter_add" near line 12

-Todd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Todd Kover <kovert (AT) omniscient (DOT) com> writes:
Hmm, that's weird ... [ cut ... paste ... ]

....
CREATE FUNCTION
regression=# select float8_jitter_add('{.1,.2,.3,1}', 5);
ERROR: function float8_jitter_add("unknown", integer) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

[ think ... ] Oh. You are creating float8_jitter_add(float8[], interval)
whereas this call is going to invoke float8_jitter_add(float8[], integer)
or something compatible with that. You're seeing a syntax error in a
different, pre-existing function with a similar name.

FWIW, 8.0 has a number of improvements in error reporting that will
hopefully make this sort of problem more transparent.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly