Thread: arrays and functions in plpgsql
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;
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
> 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?
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
Todd Kover <kovert@omniscient.com> writes:
> v_state[3] float8;
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
> > v_state[3] float8;
>
> Hmm. I'm not sure what the plpgsql parser will make of that. I think
> you probably wanted
> v_state float8[3];
oops. That was me randomly trying things I thought I understood. (I
thought I got all those :-)
> (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.
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
Todd Kover <kovert@omniscient.com> writes:
> indeed I did. Still have the same problem, though:
> create or replace function float8_jitter_add(float8[], interval)
> ...
> 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
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