pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Richard Albright <ralbright@insiderscore.com>
To: pgsql-sql@postgresql.org
Subject: plpgsql array looping
Date: Tue, 24 Apr 2007 16:42:01 -0400
Message-ID: <1177447321.17092.9.camel@MSI1036> (raw )
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
view thread (3+ messages) latest in thread
Message-ID: <1177447321.17092.9.camel@MSI1036>
Permalink: ../1177447321.17092.9.camel@MSI1036/
Also on: postgresql.org/message-id/1177447321.17092.9.camel@MSI1036
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ralbright@insiderscore.com
Subject: Re: plpgsql array looping
In-Reply-To: <1177447321.17092.9.camel@MSI1036>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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