I have figured out my looping issue, but am having difficulty wrapping
my set returning plpgsql function getmovavgset with a getmovavg sql func
when i run the following:
select getmovavg(aggarray(trade_date), aggarray(close_price),
'2004-01-20', 5)
from
( select trade_date, close_price::numeric
from quotedata
where symbol='MSFT'
and trade_date > '2004-01-01'
order by trade_date desc) values
i get the following output:
NOTICE: v_rec: ("2004-01-20 00:00:00",27.6916666666666667)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-16 00:00:00",27.6183333333333333)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-15 00:00:00",27.6766666666666667)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-14 00:00:00",27.7883333333333333)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-13 00:00:00",27.8783333333333333)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-12 00:00:00",27.9966666666666667)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-09 00:00:00",27.9766666666666667)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-08 00:00:00",28.0400000000000000)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-07 00:00:00",28.0100000000000000)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-06 00:00:00",27.9433333333333333)
CONTEXT: SQL function "getmovavg" statement 1
NOTICE: v_rec: ("2004-01-05 00:00:00",27.7950000000000000)
CONTEXT: SQL function "getmovavg" statement 1
ERROR: set-valued function called in context that cannot accept a set
I am having difficulty determining if the error is in my getmovavgset or
getmovavg function.
the notice msgs are coming from the getmovavgset func, so it is
iterating. I just dont know if the syntax is correct for the
generate_series statement in that func. What am I missing? code is
below.
CREATE TYPE resultset AS
("index" timestamp[],
"values" numeric[]);
CREATE TYPE resultsetitem AS
("index" timestamp,
value numeric);
CREATE AGGREGATE aggarray(
BASETYPE=anyelement,
SFUNC=array_append,
STYPE=anyarray,
INITCOND='{}'
);
CREATE OR REPLACE FUNCTION getmovavgarray(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
RETURNS resultset AS
$BODY$
declare
idxptr int8;
idxendptr int8;
offsetptr int8;
begoffset int8;
ar_idx timestamp[]:='{}';
ar_values numeric[]:='{}';
v_rec resultset%rowtype;
v_rtn resultset%rowtype;
v_sql text;
v_index timestamp;
v_value numeric;
v_idx timestamp;
begin
for offsetptr in 1 .. array_upper(p_idxarray, 1)
loop
--raise notice 'offset: %', offsetptr;
begoffset := offsetptr;
exit when p_idxarray[offsetptr]::timestamp <= p_idx;
end loop;
--raise notice 'offset: %, end: %', begoffset, array_upper(p_idxarray,
1);
for idxptr in 1 .. (array_upper(p_idxarray, 1) - begoffset)
loop
idxendptr := idxptr + p_periods;
v_index := p_idxarray[(idxptr + begoffset - 1)];
v_value := movavg(p_valarray[ (idxptr + begoffset - 1) :
(idxendptr +
begoffset -1) ]);
ar_idx := array_append(ar_idx, v_index);
ar_values := array_append(ar_values, v_value);
--raise notice 'idx: %, avg: %', v_index, v_value;
end loop;
v_rtn := (ar_idx, ar_values);
return v_rtn;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION getmovavgset(p_idxarray _timestamp,
p_valarray _numeric, p_idx "timestamp", p_periods int8)
RETURNS SETOF resultsetitem AS
$BODY$
declare
results resultset;
v_rec record;
v_rtn resultsetitem%rowtype;
v_sql text;
ar_idx timestamp[];
ar_values numeric[];
begin
--raise notice 'idxarray: %', p_idxarray;
for results in
select * from getmovavgarray(p_idxarray, p_valarray, p_idx,
p_periods)
loop
ar_idx := results.index;
ar_values := results.values;
end loop;
for v_rec in
select (ar_idx)[s] as index, (ar_values)[s] as value from
generate_series(1, array_upper(ar_idx, 1)) as s
loop
raise notice 'v_rec: %', v_rec;
v_rtn := (v_rec.index, v_rec.value);
--raise notice 'resultset: %', v_rtn;
return next v_rtn;
end loop;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE OR REPLACE FUNCTION getmovavg(p_idxarray _timestamp, p_valarray
_numeric, p_idx "timestamp", p_periods int8)
RETURNS SETOF resultsetitem as
$BODY$
select * from getmovavgset($1, $2, $3, $4);
$BODY$
LANGUAGE 'sql' volatile;
--
Rick Albright
Senior Quantitative Analyst
Indie Research, LLC
254 Witherspoon Street
Princeton, NJ 08542
(609)497-1030
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
