Обсуждение: function returning a merge of the same query executed X time
Hi all, I'm having difficulties to create a function that should execute X time the same query and return their results as a singletable. I tried this way but it don't work : Thanks to help. create function cm_get_json_loop_info( IN profile integer, IN distance double precision DEFAULT 10, IN x_start double precision DEFAULT 0, IN y_start double precision DEFAULT 0, IN nbr integer default 1) returns setof json as $BODY$ declare jsona json; BEGIN for json a i in 1.. nbr loop select row_to_json(q) from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r))as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q return next jsona end loop; return jsona; Marc
On 4/22/15 8:14 AM, Marc-André Goderre wrote: > select row_to_json(q) > from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r))as data > from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q Untested... CREATE FUNCTION ...( ... , iterations int ) LANGUAGE sql AS $body$ select row_to_json(q) from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q , generate_series(1, iterations) i $body$ -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
Can you not just CROSS JOIN it to generate_series(1, 8)?
On 22 April 2015 at 14:14, Marc-André Goderre <magoderre@cgq.qc.ca> wrote:
Hi all,
I'm having difficulties to create a function that should execute X time the same query and return their results as a single table.
I tried this way but it don't work :
Thanks to help.
create function cm_get_json_loop_info(
IN profile integer,
IN distance double precision DEFAULT 10,
IN x_start double precision DEFAULT 0,
IN y_start double precision DEFAULT 0,
IN nbr integer default 1)
returns setof json as
$BODY$
declare jsona json;
BEGIN
for json a i in 1.. nbr loop
select row_to_json(q)
from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data
from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q
return next jsona
end loop;
return jsona;
Marc
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 04/22/2015 06:14 AM, Marc-André Goderre wrote: > Hi all, > I'm having difficulties to create a function that should execute X time the same query and return their results as asingle table. > I tried this way but it don't work : > Thanks to help. See comments inline. > > create function cm_get_json_loop_info( > IN profile integer, > IN distance double precision DEFAULT 10, > IN x_start double precision DEFAULT 0, > IN y_start double precision DEFAULT 0, > IN nbr integer default 1) > > returns setof json as > $BODY$ > declare jsona json; > BEGIN > for json a i in 1.. nbr loop ^^^^^^^ Not your declared variable. Besides which if the variable where correct you would just be assigning it the numbers 1 through nbr. See: http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS 40.6.3.5. FOR (Integer Variant) > > select row_to_json(q) > from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r))as data > from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q > return next jsona The output of the query is not assigned to jsona. http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT 40.5.3. Executing a Query with a Single-row Result > end loop; > return jsona; This should be just RETURN. http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING 40.6.1.2. RETURN NEXT and RETURN QUERY > > Marc > > > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks Geoff for your idea but my query return something different each time I call it. This way, select row_number()over() as id,q.* from (select sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q CROSS JOIN generate_series(1, 3) the quey is executed only 1 time. Thanks Marc De : gwinkless@gmail.com [mailto:gwinkless@gmail.com] De la part de Geoff Winkless Envoyé : 22 avril 2015 11:22 À : Marc-André Goderre Cc : 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org) Objet : Re: [GENERAL] function returning a merge of the same query executed X time Can you not just CROSS JOIN it to generate_series(1, 8)? On 22 April 2015 at 14:14, Marc-André Goderre <magoderre@cgq.qc.ca> wrote: Hi all, I'm having difficulties to create a function that should execute X time the same query and return their results as a singletable. I tried this way but it don't work : Thanks to help. create function cm_get_json_loop_info( IN profile integer, IN distance double precision DEFAULT 10, IN x_start double precision DEFAULT 0, IN y_start double precision DEFAULT 0, IN nbr integer default 1) returns setof json as $BODY$ declare jsona json; BEGIN for json a i in 1.. nbr loop select row_to_json(q) from (select row_number() over() as id, sum(cost) as total_cost,sum(length) as total_length,json_agg(row_to_json(r))as data from (select * from cm_get_loop_route_4(2, 10, -73.597070, 45.544083))r)q return next jsona end loop; return jsona; Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general