From: | Marc-André Goderre <magoderre(at)cgq(dot)qc(dot)ca> |
---|---|
To: | Geoff Winkless <pgsqladmin(at)geoff(dot)dj> |
Cc: | "'pgsql-general(at)postgresql(dot)org' (pgsql-general(at)postgresql(dot)org)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: function returning a merge of the same query executed X time |
Date: | 2015-04-23 17:21:14 |
Message-ID: | D8DAC91BA6256246BA3FF835F6AAA1189943F425@SI-MB01.cegep-chicoutimi.qc.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com [mailto:gwinkless(at)gmail(dot)com] De la part de Geoff Winkless
Envoyé : 22 avril 2015 11:22
À : Marc-André Goderre
Cc : 'pgsql-general(at)postgresql(dot)org' (pgsql-general(at)postgresql(dot)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(at)cgq(dot)qc(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Atkins | 2015-04-23 17:22:56 | Re: Moving Specific Data Across Schemas Including FKs |
Previous Message | Cory Tucker | 2015-04-23 17:09:20 | Moving Specific Data Across Schemas Including FKs |