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 a single 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
