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_date asc) 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$
declare
idxptr int8;
idxendptr int8;
v_rec record;
v_rtn resultset%rowtype;
v_sql text;
begin
raise 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
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
