Thread: plpgsql array looping

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

One problem (unless you intend to only look at every other element)  
is that you are incrementing idxptr explicitly in your loop. The FOR  
loop does that for you. This is the reason your output shows only  
even values.

John


On Apr 24, 2007, at 4:42 PM, Richard Albright wrote:

>     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;



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

yeah i noticed that this morning, it used to be a while loop, for some
reason (probably parser related) it keeps giving me an error on the exit
when statement in the loop.

On Wed, 2007-04-25 at 09:38 -0400, John DeSoi wrote:
> One problem (unless you intend to only look at every other element)  
> is that you are incrementing idxptr explicitly in your loop. The FOR  
> loop does that for you. This is the reason your output shows only  
> even values.
> 
> John
> 
> 
> On Apr 24, 2007, at 4:42 PM, Richard Albright wrote:
> 
> >     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;
> 
> 
> 
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate
-- 
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
ralbright@insiderscore.com