From: | "Lucas F(dot)" <lucasf(at)vagabond-software(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | syntax issue with custom aggregator |
Date: | 2005-03-25 19:17:29 |
Message-ID: | 016001c5316f$49be8240$a001a8c0@sf.vagabond.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
When I run a query using a custom aggregator I wrote to find the average of
only non-negative values:
CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2",
SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4",
FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'");
CREATE OR REPLACE FUNCTION "property"."ag_pos_avg_accum" (p_state integer
[], p_input smallint) RETURNS integer [] AS
$body$
/* state transition function for the pos_avg custom aggregate*/
declare
state_copy integer[];
begin
state_copy := p_state;
if (p_state is null) then
state_copy := '{0,0}';
state_copy[0] := 0;
state_copy[1] := 0;
end if;
if (p_input >= 0 and not p_input is null) then
/* number of records */
state_copy[0] := state_copy[0] + 1;
/* running total */
state_copy[1] := state_copy[1] + p_input;
end if;
return state_copy;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "property"."ag_pos_avg_final" (p_state integer
[]) RETURNS smallint AS
$body$
/* final function for pos_avg custom aggregator */
declare
ret smallint;
begin
if (p_state[0] != 0) then
ret := p_state[1] / p_state[0];
else
ret := null;
end if;
return ret;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I get the error:
ERROR: array value must start with "{" or dimension information
I'm pretty new to postgresql so it's probably something obvious, but I'm at
a loss as to what it is...
Thank you...
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus Moor | 2005-03-25 19:23:12 | Merging item codes using referential integrity |
Previous Message | Jim Buttafuoco | 2005-03-25 19:00:55 | Re: Stuck with references |