Subject: Re: sql wrapped plpgsql set returning function
From: Richard Albright <ralbright@insiderscore.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <1177524609.17790.1.camel@MSI1036>
References: <1177524609.17790.1.camel@MSI1036>
Content-Type: text/plain
Date: Wed, 25 Apr 2007 14:45:05 -0400
Message-Id: <1177526705.17790.8.camel@MSI1036>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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