Thread: sql wrapped plpgsql set returning function
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_datedesc) 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$ declareidxptr int8;idxendptr int8;offsetptr int8;begoffset int8;ar_idx timestamp[]:='{}';ar_values numeric[]:='{}';v_recresultset%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); --raisenotice '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$ declareresults 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 sloop 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 ralbright@insiderscore.com
Richard Albright <ralbright@insiderscore.com> writes: > 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: Hm, worksforme (see attached trivial example). What PG version are you using? regards, tom lane regression=# create function foo() returns setof int8_tbl as $$ regression$# declare r record; regression$# begin regression$# for r in select * from int8_tbl loop regression$# return next r; regression$# end loop; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select * from foo(); q1 | q2 ------------------+------------------- 123 | 456 123 | 45678901234567894567890123456789| 1234567890123456789 | 45678901234567894567890123456789 | -4567890123456789 (5 rows) regression=# select foo(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "foo" line 4 at return next regression=# create function foowrap() returns setof int8_tbl as $$ regression$# select * from foo(); regression$# $$ language sql; CREATE FUNCTION regression=# select foowrap(); foowrap --------------------------------------(123,456)(123,4567890123456789)(4567890123456789,123)(4567890123456789,4567890123456789)(4567890123456789,-4567890123456789) (5 rows) regression=#
It turns out that the from subselect is causing the error in : 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 whereas select * from getmovavg(array['2007-04-03', '2007-04-02', '2007-04-01']::timestamp[], array[4.0,3.0,2.0]::numeric[], '2007-04-03', 3) will work. anyone know why that would be? On Wed, 2007-04-25 at 14:10 -0400, Richard Albright wrote: > 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 ralbright@insiderscore.com
I narrowed it down further. Can someone explain the difference between passing array[...] and passing an array using an aggregate array function into the function? On Wed, 2007-04-25 at 14:45 -0400, Richard Albright wrote: > It turns out that the from subselect is causing the error in : > > 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 > > whereas > > select * from getmovavg(array['2007-04-03', '2007-04-02', > '2007-04-01']::timestamp[], array[4.0,3.0,2.0]::numeric[], '2007-04-03', > 3) > > will work. anyone know why that would be? > > On Wed, 2007-04-25 at 14:10 -0400, Richard Albright wrote: > > 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 ralbright@insiderscore.com