Subject: plpgsql array looping
From: Richard Albright <ralbright@insiderscore.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Date: Tue, 24 Apr 2007 16:42:01 -0400
Message-Id: <1177447321.17092.9.camel@MSI1036>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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
ralbright@insiderscore.com