pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql array looping
3+ messages / 2 participants
[nested] [flat]
* plpgsql array looping
@ 2007-04-24 20:42 Richard Albright <ralbright@insiderscore.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Albright @ 2007-04-24 20:42 UTC (permalink / raw)
To: 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@insiderscore.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql array looping
@ 2007-04-25 13:38 John DeSoi <desoi@pgedit.com>
parent: Richard Albright <ralbright@insiderscore.com>
0 siblings, 1 reply; 3+ messages in thread
From: John DeSoi @ 2007-04-25 13:38 UTC (permalink / raw)
To: Richard Albright <ralbright@insiderscore.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: plpgsql array looping
@ 2007-04-25 13:54 Richard Albright <ralbright@insiderscore.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Albright @ 2007-04-25 13:54 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-25 13:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-24 20:42 plpgsql array looping Richard Albright <ralbright@insiderscore.com>
2007-04-25 13:38 ` John DeSoi <desoi@pgedit.com>
2007-04-25 13:54 ` Richard Albright <ralbright@insiderscore.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox