sql wrapped plpgsql set returning function - Mailing list pgsql-sql

I have figured out my looping issue, but am having difficulty wrapping
my set returning plpgsql function getmovavgset with a getmovavg sql func
when i run the following:

select getmovavg(aggarray(trade_date), aggarray(close_price),
'2004-01-20', 5)
from ( select trade_date, close_price::numeric from quotedata where symbol='MSFT'and trade_date > '2004-01-01'order by
trade_datedesc) values 
 

i get the following output:

NOTICE:  v_rec: ("2004-01-20 00:00:00",27.6916666666666667)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-16 00:00:00",27.6183333333333333)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-15 00:00:00",27.6766666666666667)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-14 00:00:00",27.7883333333333333)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-13 00:00:00",27.8783333333333333)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-12 00:00:00",27.9966666666666667)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-09 00:00:00",27.9766666666666667)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-08 00:00:00",28.0400000000000000)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-07 00:00:00",28.0100000000000000)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-06 00:00:00",27.9433333333333333)
CONTEXT:  SQL function "getmovavg" statement 1
NOTICE:  v_rec: ("2004-01-05 00:00:00",27.7950000000000000)
CONTEXT:  SQL function "getmovavg" statement 1

ERROR:  set-valued function called in context that cannot accept a set

I am having difficulty determining if the error is in my getmovavgset or
getmovavg function.
the notice msgs are coming from the getmovavgset func, so it is
iterating. I just dont know if the syntax is correct for the
generate_series statement in that func.  What am I missing? code is
below.

CREATE TYPE resultset AS  ("index" timestamp[],   "values" numeric[]);

CREATE TYPE resultsetitem AS  ("index" timestamp,   value numeric);

CREATE AGGREGATE aggarray( BASETYPE=anyelement, SFUNC=array_append, STYPE=anyarray, INITCOND='{}'
);

CREATE OR REPLACE FUNCTION getmovavgarray(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8) RETURNS resultset AS
$BODY$
declareidxptr int8;idxendptr int8;offsetptr int8;begoffset int8;ar_idx timestamp[]:='{}';ar_values
numeric[]:='{}';v_recresultset%rowtype;v_rtn resultset%rowtype;v_sql text;v_index timestamp;v_value numeric;v_idx
timestamp;
begin        for offsetptr in 1 .. array_upper(p_idxarray, 1)loop    --raise notice 'offset: %', offsetptr;
begoffset:= offsetptr;    exit when p_idxarray[offsetptr]::timestamp <= p_idx;end loop;--raise notice 'offset: %, end:
%',begoffset, array_upper(p_idxarray,
 
1);for idxptr in 1 .. (array_upper(p_idxarray, 1) - begoffset)loop    idxendptr := idxptr + p_periods;        v_index
:=p_idxarray[(idxptr + begoffset - 1)];    v_value := movavg(p_valarray[ (idxptr + begoffset - 1) : (idxendptr +
 
begoffset -1) ]);    ar_idx := array_append(ar_idx, v_index);    ar_values := array_append(ar_values, v_value);
--raisenotice 'idx: %, avg: %', v_index, v_value;end loop;v_rtn := (ar_idx, ar_values);return v_rtn;
 
end
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION getmovavgset(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8) RETURNS SETOF resultsetitem AS
$BODY$
declareresults resultset;v_rec record;v_rtn resultsetitem%rowtype;v_sql text;ar_idx timestamp[];ar_values numeric[];
begin--raise notice 'idxarray: %', p_idxarray;for results in     select * from getmovavgarray(p_idxarray, p_valarray,
p_idx,p_periods)loop    ar_idx := results.index;    ar_values := results.values;end loop;for v_rec in select
(ar_idx)[s]as index, (ar_values)[s] as value from
 
generate_series(1, array_upper(ar_idx, 1)) as sloop    raise notice 'v_rec: %', v_rec;    v_rtn := (v_rec.index,
v_rec.value);   --raise notice 'resultset: %', v_rtn;    return next v_rtn;end loop;
 
end
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION getmovavg(p_idxarray _timestamp, p_valarray
_numeric, p_idx "timestamp", p_periods int8) RETURNS SETOF resultsetitem as
$BODY$select * from getmovavgset($1, $2, $3, $4);
$BODY$LANGUAGE 'sql' volatile;
-- 
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
ralbright@insiderscore.com