I am attempting to create a moving average elements function that will
return a set of elements in a particular moving average and am having
difficulty iterating thrrough the passed in array properly. Any help
would be greatly appreciated.
code below...
select getmovavgelements( aggarray(trade_date), aggarray(close_price),
'2004-02-10'::timestamp, 10 )
from ( select trade_date, close_price::numeric from quotedata where symbol='MSFT'and trade_date > '2004-01-01'order by
trade_dateasc) values;
NOTICE: idx: {"2004-01-02 00:00:00","2004-01-05 00:00:00",...}
NOTICE: vals: {27.45,28.14,...}
NOTICE: maxdate: 2004-02-10 00:00:00
NOTICE: dims: [1:821]
NOTICE: idx ptr: 2
NOTICE: idx ptr: 4
NOTICE: idx ptr: 6
NOTICE: idx ptr: 8
NOTICE: idx ptr: 10
NOTICE: idx ptr: 12
NOTICE: idx ptr: 14
NOTICE: idx ptr: 16
NOTICE: idx ptr: 18
NOTICE: idx ptr: 20
NOTICE: idx ptr: 22
NOTICE: idx ptr: 24
NOTICE: idx ptr: 26
NOTICE: row: 2004-01-02 00:00:00 27.45
ERROR: array value must start with "{" or dimension information
CONTEXT: PL/pgSQL function "getmovavgelements" line 25 at assignment
---
CREATE OR REPLACE FUNCTION getmovavgelements(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8) RETURNS setof resultset AS
$BODY$
declareidxptr int8;idxendptr int8;v_rec record;v_rtn resultset%rowtype;v_sql text;
beginraise notice 'idx: %', p_idxarray;raise notice 'vals: %', p_valarray;idxptr := array_lower(p_idxarray, 1);raise
notice'maxdate: %', p_idx;raise notice 'dims: % ', array_dims(p_idxarray); for idxptr in 1 ..
array_upper(p_idxarray,1) loop exit when p_idxarray[idxptr] >= p_idx; idxptr := idxptr +1; raise notice 'idx
ptr:%', idxptr;end loop;idxendptr := idxptr + p_periods; for v_rec in select s.ser, p_idxarray[s.ser] as index,
p_valarray[s.ser]as value
from generate_series(idxptr, idxendptr) as s(ser)loop raise notice 'row: % %', v_rec.index, v_rec.value; v_rtn :=
(v_rec.index,v_rec.value); return next v_rtn;end loop;
end
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
--
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
ralbright@insiderscore.com
