plpgsql array looping

From: Richard Albright <ralbright(at)insiderscore(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: plpgsql array looping
Date: 2007-04-24 20:42:01
Message-ID: 1177447321.17092.9.camel@MSI1036
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)insiderscore(dot)com

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Aaron Bono 2007-04-25 01:26:15 Query Join Performance
Previous Message Stephan Szabo 2007-04-24 16:41:26 Re: hi