Обсуждение: sql wrapped plpgsql set returning function

Поиск
Список
Период
Сортировка

sql wrapped plpgsql set returning function

От
Richard Albright
Дата:
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

Re: sql wrapped plpgsql set returning function

От
Tom Lane
Дата:
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=#

Re: sql wrapped plpgsql set returning function

От
Richard Albright
Дата:
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

Re: sql wrapped plpgsql set returning function

От
Richard Albright
Дата:
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

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.