Thread: syntax issue with custom aggregator

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...

"Lucas F." <lucasf@vagabond-software.com> writes:
> CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2",
> SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4",
> FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'");

Too many quotes ... try INITCOND = '{0,0}'

            regards, tom lane

On Fri, Mar 25, 2005 at 02:39:11PM -0500, Tom Lane wrote:
> "Lucas F." <lucasf@vagabond-software.com> writes:
> > CREATE AGGREGATE "property"."pos_avg" ( BASETYPE = "int2",
> > SFUNC = "property"."ag_pos_avg_accum", STYPE = "_int4",
> > FINALFUNC = "property"."ag_pos_avg_final", INITCOND = "'{0,0}'");
>
> Too many quotes ... try INITCOND = '{0,0}'

Also, check the array subscripts in your functions.  By default,
PostgreSQL array subscripts start at 1, not 0.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/