pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsql wrapped plpgsql set returning function
4+ messages / 2 participants
[nested] [flat]
* sql wrapped plpgsql set returning function
@ 2007-04-25 18:10 Richard Albright <ralbright@insiderscore.com>
0 siblings, 2 replies; 4+ messages in thread
From: Richard Albright @ 2007-04-25 18:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql wrapped plpgsql set returning function
@ 2007-04-25 18:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Albright <ralbright@insiderscore.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-04-25 18:32 UTC (permalink / raw)
To: Richard Albright <ralbright@insiderscore.com>; +Cc: pgsql-sql
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 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -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=#
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql wrapped plpgsql set returning function
@ 2007-04-25 18:45 Richard Albright <ralbright@insiderscore.com>
parent: Richard Albright <ralbright@insiderscore.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Albright @ 2007-04-25 18:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sql wrapped plpgsql set returning function
@ 2007-04-25 19:36 Richard Albright <ralbright@insiderscore.com>
parent: Richard Albright <ralbright@insiderscore.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Albright @ 2007-04-25 19:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-04-25 19:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-25 18:10 sql wrapped plpgsql set returning function Richard Albright <ralbright@insiderscore.com>
2007-04-25 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-25 18:45 ` Richard Albright <ralbright@insiderscore.com>
2007-04-25 19:36 ` Richard Albright <ralbright@insiderscore.com>
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